Use MySQL String Functions to Build Printable ASCII Characters Chart

Use MySQL String Functions to Build Printable ASCII Character Chart

Printable ASCII Characters

ASCII stands for American Standard Code for Information Interchange. ASCII is the numerical representation of characters based on the English alphabet characters and non-printing characters. Table below lists the character encoding chart of 94 printable ASCII characters (excluding the space character), numbered from 33 to 126 in decimal base-10 number system.

Binary	Octal	Decimal	Hexadecimal	Character
100000	40	32	20	space character
100001	41	33	21	!
100010	42	34	22	"
100011	43	35	23	#
100100	44	36	24	$
100101	45	37	25	%
100110	46	38	26	&
100111	47	39	27	'
101000	50	40	28	(
101001	51	41	29	)
101010	52	42	2A	*
101011	53	43	2B	+
101100	54	44	2C	,
101101	55	45	2D	-
101110	56	46	2E	.
101111	57	47	2F	/
110000	60	48	30	0
110001	61	49	31	1
110010	62	50	32	2
110011	63	51	33	3
110100	64	52	34	4
110101	65	53	35	5
110110	66	54	36	6
110111	67	55	37	7
111000	70	56	38	8
111001	71	57	39	9
111010	72	58	3A	:
111011	73	59	3B	;
111100	74	60	3C	<
111101	75	61	3D	=
111110	76	62	3E	>
111111	77	63	3F	?
1000000	100	64	40	@
1000001	101	65	41	A
1000010	102	66	42	B
1000011	103	67	43	C
1000100	104	68	44	D
1000101	105	69	45	E
1000110	106	70	46	F
1000111	107	71	47	G
1001000	110	72	48	H
1001001	111	73	49	I
1001010	112	74	4A	J
1001011	113	75	4B	K
1001100	114	76	4C	L
1001101	115	77	4D	M
1001110	116	78	4E	N
1001111	117	79	4F	O
1010000	120	80	50	P
1010001	121	81	51	Q
1010010	122	82	52	R
1010011	123	83	53	S
1010100	124	84	54	T
1010101	125	85	55	U
1010110	126	86	56	V
1010111	127	87	57	W
1011000	130	88	58	X
1011001	131	89	59	Y
1011010	132	90	5A	Z
1011011	133	91	5B	[
1011100	134	92	5C	\
1011101	135	93	5D	]
1011110	136	94	5E	^
1011111	137	95	5F	_
1100000	140	96	60	`
1100001	141	97	61	a
1100010	142	98	62	b
1100011	143	99	63	c
1100100	144	100	64	d
1100101	145	101	65	e
1100110	146	102	66	f
1100111	147	103	67	g
1101000	150	104	68	h
1101001	151	105	69	i
1101010	152	106	6A	j
1101011	153	107	6B	k
1101100	154	108	6C	l
1101101	155	109	6D	m
1101110	156	110	6E	n
1101111	157	111	6F	o
1110000	160	112	70	p
1110001	161	113	71	q
1110010	162	114	72	r
1110011	163	115	73	s
1110100	164	116	74	t
1110101	165	117	75	u
1110110	166	118	76	v
1110111	167	119	77	w
1111000	170	120	78	x
1111001	171	121	79	y
1111010	172	122	7A	z
1111011	173	123	7B	{
1111100	174	124	7C	\|
1111101	175	125	7D	}
1111110	176	126	7E	~

Build the ASCII table by using MySQL string functions

To create a table like shown above, you only need to create a table in MySQL that stores the 94 printable characters. The you use five MySQL string functions (BIN, OCT, ASCII, HEX, SPACE) to convert the printable characters to their representations in the different number system.

Name	Number System
Binary	Base 2
Octal	Base 8
Decimal	Base 10
Hexadecimal	Base 16

Follow the steps below to build the ASCII table.

Step #1: Build a table to store the 94 printable characters

To copy and paste the query to SQLyog, use Firefox. Copying from Internet Explorer does not maintain the query format.

-- Build the table
CREATE TABLE `printable_char` 
(
  `id` tinyint(3) unsigned NOT NULL,
  `single_char` char(1) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

Step #2: Bulk Insert the 94 printable characters

insert  into `printable_char`(`id`,`single_char`) 
values (1,'!'),(2,'\"'),(3,'#'),(4,'$'),(5,'%'),(6,'&'),(7,'\''),(8,'('),
(9,')'),(10,'*'),(11,'+'),(12,','),(13,'-'),(14,'.'),(15,'/'),(16,'0'),
(17,'1'),(18,'2'),(19,'3'),(20,'4'),(21,'5'),(22,'6'),(23,'7'),(24,'8'),
(25,'9'),(26,':'),(27,';'),(28,'<'),(29,'='),(30,'>'),(31,'?'),(32,'@'),
(33,'A'),(34,'B'),(35,'C'),(36,'D'),(37,'E'),(38,'F'),(39,'G'),(40,'H'),
(41,'I'),(42,'J'),(43,'K'),(44,'L'),(45,'M'),(46,'N'),(47,'O'),(48,'P'),
(49,'Q'),(50,'R'),(51,'S'),(52,'T'),(53,'U'),(54,'V'),(55,'W'),(56,'X'),
(57,'Y'),(58,'Z'),(59,'['),(60,'\\'),(61,']'),(62,'^'),(63,'_'),(64,'`'),
(65,'a'),(66,'b'),(67,'c'),(68,'d'),(69,'e'),(70,'f'),(71,'g'),(72,'h'),
(73,'i'),(74,'j'),(75,'k'),(76,'l'),(77,'m'),(78,'n'),(79,'o'),(80,'p'),
(81,'q'),(82,'r'),(83,'s'),(84,'t'),(85,'u'),(86,'v'),(87,'w'),(88,'x'),
(89,'y'),(90,'z'),(91,'{'),(92,'|'),(93,'}'),(94,'~');

Step #3: Create the query to retrieve the 94 printable characters and convert them to their other representations.

/*
There are two SELECT statements unioned together. 
The first one returns values for space character.
 
The second one build the ASCII table for
the 94 printable characters.
*/
SELECT BIN(ASCII(SPACE(1))) AS binary_val, 
    OCT(ASCII(SPACE(1))) AS octal_val, 
    ASCII(SPACE(1)) AS decimal_val, 
    HEX(SPACE(1)) AS hex_val, 
    'SPACE character' AS single_char 
UNION ALL
SELECT BIN(ASCII(single_char)) AS binary_val, 
    OCT(ASCII(single_char)) AS octal_val, 
    ASCII(single_char) AS decimal_val, 
    HEX(single_char) AS hex_val, 
    single_char 
FROM printable_char;

More detailed information about the five string functions (BIN, OCT, ASCII, HEX, SPACE) used in the query above, refer to MySQL String Functions section.

Other Recent Articles from the Web Development category:

1.	Connect to a MySQL Database from PHP version 5 or later versions
2.	Java / JSP lost session value on redirect - FIXED
3.	Fix the problem with PHP5 XML removeChild() method
4.	How to integrate PHP HTML Help .chm file with Crimson Editor
5.	How to Connect to a MySQL Database from PHP (version lower than PHP 5.0.0)
6.	Five ways to create include path for PHP
7.	How to use Date and Time data as integer value in PHP and MySQL
8.	Absolute Path and Relative Path Explained

	Custom Search