Use MySQL String Functions, Part 1

Using String Functions, Part 1

String Functions are used to compare strings, search and/or replace substrings, modify a string, convert a string to a different format, and so on. Even though MySQL server is not specially designed to handle strings, there is a rich set of string functions available.

If it's handy to use a string function inside your SQL statement, do use it. If the string operation is too heavy for database server to handle, you'd better leave string manipulation for the client (such as PHP) to do.

Here are the links to Part 2 and 3:

Here is the list of MySQL String Functions for Part 1:

No.	Name	Description	Practice
1	ASCII(str)	Returns the ASCII numeric value (the decimal base-10 number system) of left-most character of the string str. ASCII stands for American Standard Code for Information Interchange, which is a character encoding based on the English alphabet characters and non-printing characters. There are 94 printable ASCII characters, numbered 32 to 126 (decimal number system). ASCII() returns the decimal value for a given printable character. Refer to article Use MySQL String Functions to Build ASCII Character Chart for more information about the ASCII numerical representation.	#1
2	BIN(N)	Returns a string representation of the binary value for integer N. Returns NULL if N is NULL. BIN(N) is equivalent to CONV(N,10,2).	#2
3	BIT_LENGTH(str)	Returns the length of the string str in bits.	#3
4	CHAR_LENGTH(str)	Returns the number of characters in str. The difference between CHAR_LENGTH() and LENGTH() function: For English alphabet characters, because they use 1 byte per character, CHAR_LENGTH returns the number of characters in the argument str. See Practice #4-1. For multi-byte characters, CHAR_LENGTH counts each multi-byte character as a single character. This means that for a string containing two three-byte characters, CHAR_LENGTH() returns 2. This is different to how LENGTH() function counts the length of a string, which measures a string in bytes. So for a string containing two three-byte characters, LENGTH() returns 6. See Practice #4-2. See LENGTH() function to compare how it's different to CHAR_LENGTH function in terms of handling the length for multi-byte characters.
5	CHAR(N,...)	Returns the character for each ASCII integer value passed into the argument in comma delimited fashion.	#5
6	CHARACTER_LENGTH(str)	A synonym for CHAR_LENGTH(). See Practice #4-1 and Practice #4-2.
7	CONCAT(str1,str2,...)	Returns the string that results from concatenating the passed in argument str1, str2... May have one or more arguments. See Practice #7-1. If an argument is numeric number, it is automatically converted to a string. See Practice #7-2. CONCAT() returns NULL if any argument is NULL. See Practice #7-3. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast. A binary string stores a sequence of data values, usually bytes. A binary string is not like a character string which usually contains text data. Binary strings are used to hold data that does not (necessarily) represent text. Binary strings normally store data for pictures, voice, or mixed media. An example of binary string in MySQL is BLOB (Binary Large Object) data type. BLOB is a varying-length binary string.
8	CONCAT_WS(str1,str2,...)	CONCAT_WS stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. In the concatenated result, the separator is added between each of the other strings in the argument list. See Practice #8-1. If the separator is NULL, the result is NULL. See Practice #8-2.
9	ELT(N,str1,str2,str3,...)	Returns the string at index number position (N) of the string list str1, str2, str3... If N is 1, str1 is returned. If N is 2, str2 is returned. If N is 3, str3 is returned, and so on. See Practice #9-1. If N is less than 1 but can be rounded to 1, the first string in the string list is returned. See Practice #9-2. If N is less than 1 and cannot be rounded to 1, ELT() returns NULL. See Practice #9-3. If N is greater than the number of arguments in the string list, ELT() returns NULL. See Practice #9-4. ELT() is the complement of FIELD() function. See below.
10	EXPORT_SET(bits, on, off [,separator [,number_of_bits]])	Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string. Bits (the string representation of the binary value for bits) in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character ","). The number of bits examined is given by number_of_bits (defaults to 64).	#10
11	FIELD(str,str1,str2,str3,...)	Returns the index position of str in the str1, str2, str3, ... list. Returns 0 if str is not found. See Practice #11-1. If multiple str is found in the argument list, the first argument in the subsequent arguments is returned. See Practice #11-2. If str is NULL, the return value is 0 because NULL fails equality comparison with any value. See Practice #11-3. String comparison in FIELD() function is not case-sensitive. FIELD() is the complement of ELT(). See Practice #11-4. If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.
12	FIND_IN_SET(str,strlist)	Return the index position number of the first argument str within the second argument strlist. The index position number is in the range of 1 to N. The string list strlist consists of a number of substrings separated by 0 or more commas. Therefore, strlist is a CSV (Comma Separated Values) list. See Practice #12-1. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. FIND_IN_SET returns 0 if str is not in strlist or if strlist is an empty string. FIND_IN_SET returns NULL if either str or strlist is NULL. See Practice #12-2. This function does not work properly if the first argument contains a comma (",") character.
13	FORMAT(X,D)	Formats the number X to a format like '#,###,###.###', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. See Practice #13-1. FORMAT() also rounds up for the last digit in the formatted result. See Practice #13-2.
14	HEX(N or S)	Returns a hexadecimal representation of a decimal or string value. If the argument is a number, HEX() returns the number's string representation of the hexadecimal value. This is equivalent to conversion function CONV(N,10,16). See Practice #14-1. If the argument is a string, HEX() returns the string's hexadecimal string representation where each character in the string is converted to two hexadecimal digits. See Practice #14-2. The inverse of this operation is performed by the UNHEX() function.
15	INSERT(original_str, pos, len, replace_str)	Inserts a substring at the specified position up to the specified number of characters. Replace the original_str with the substring beginning at position pos and len characters long by the string replace_str. See Practice #15-1. Returns the original string if pos is not within the length of the original_str. See Practice #15-2. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. See Practice #15-3. Returns NULL if any argument is NULL.

Practice #1: Using ASCII string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
Return the ASCII value of the first letter
in product names.
*/
SELECT ProductName, 
       LEFT(ProductName,1) AS "First Letter",
       ASCII(ProductName) AS "First Letter ASCII"
FROM products;

Query result set - 77 rows returned:
Result of ASCII function

Practice #2: Using BIN string function

-- Get binary representation for integer 23764
SELECT BIN(23764) AS "Binary value";

Query result set - 1 row returned:
Result of BIN function

Practice #3: Using BIT_LENGTH string function

/*
This query returns 40 bits because a single 
character from a Western character set is 8 bits.
Geeks has 5 characters so 5 x 8 = 40
*/
SELECT BIT_LENGTH('Geeks');

Query result set - 1 row returned:
Result of using BIT_LENGTH string function

Practice #4-1: Using CHAR_LENGTH string function for English characters (1-byte)

/*
For English alphabet characters, because they 
use 1 byte per character, CHAR_LENGTH returns the 
number of characters in the argument.
 
This query returns 5 for the string Geeks.
*/
SELECT CHAR_LENGTH('Geeks');

Query result set - 1 row returned:
char_length function for English characters

Practice #4-2: Using CHAR_LENGTH string function for multi-byte characters

/*
For multi-byte characters, CHAR_LENGTH counts 
each multi-byte character as a single character.
 
Chinese characters use 3 bytes per character. So 
this query returns 2 for two Chinese characters 汉字.
*/
SELECT CHAR_LENGTH('汉字');

Query result set - 1 row returned:
char_length function for multi-byte characters

Practice #5: Using CHAR() string function

/*
This query converts ASCII integer values (the decimal 
base-10 number system) into their corresponding characters.
This query returns GeeksEngine
*/
SELECT CHAR(71,101,101,107,115,69,110,103,105,110,101);

Query result set - 1 row returned:
Using CHAR function

Practice #7-1: Using CONCAT() string function

-- Concatenate three strings
SELECT CONCAT('Geeks', 'Engine', '.com');

Query result set - 1 row returned:
Using CONCAT function

Practice #7-2: Using CONCAT() string function for numeric values

/*
This query concatenates strings with numeric values
and the numeric values are automatically converted to strings.
*/
SELECT CONCAT('MySQL ', 5.1, ' has more functions than 5.0 release.');

Query result set - 1 row returned:
Using CONCAT function for numeric number

Practice #7-3: Using CONCAT() string function for NULL values

/*
If one of the values in the argument list 
is NULL, the concatenated result is NULL.
*/
SELECT CONCAT('Web', 'site', NULL);

Query result set - 1 row returned:
Using CONCAT function for numeric number

Practice #8-1: Using CONCAT_WS() string function

/*
In this query, the separator is a comma and the concatenated 
result a string of CSV (Comma Separated Values).
*/
SELECT CONCAT_WS(',', 'music', 'outdoors', 'movies');

Query result set - 1 row returned:
Using CONCAT_WS function

Practice #8-2: Using CONCAT_WS() string function and use NULL as the separator

/*
In this query, the separator is NULL and so the 
result.
*/
SELECT CONCAT_WS(NULL, 'music', 'outdoors', 'movies');

Query result set - 1 row returned:
Using CONCAT_WS function and use NULL as the separator

Practice #9-1: Using ELT() string function

-- Returns the second string in the list
SELECT ELT(2, 'music', 'outdoors', 'movies');

Query result set - 1 row returned:
Using ELT function

Practice #9-2: Using ELT() string function and the position number can be rounded to one

/*
This query returns the first string in the list because
0.51 can be rounded to 1.
*/
SELECT ELT(0.51, 'music', 'outdoors', 'movies');

Query result set - 1 row returned:
Using ELT function and the position number can be rounded to one

Practice #9-3: Using ELT() string function and the position number cannot be rounded to one

/*
This query returns NULL because 0.49 is rounded to 0.
*/
SELECT ELT(0.49, 'music', 'outdoors', 'movies');

Query result set - 1 row returned:
Using ELT function and the position number cannot be rounded to one

Practice #9-4: Using ELT() string function and the position number is greater than the number of elements in the string list

/*
This query returns NULL because position index 
number 4 does not exist in the string list.
*/
SELECT ELT(4, 'music', 'outdoors', 'movies');

Query result set - 1 row returned:
Using ELT function and the position number is greater than the number of elements in the string list

Practice #10: Using EXPORT_SET string function

/*
This query returns a string such that for every bit set in 
the value 18 (the string representation of the binary value 
for number 18), you get a Y and for every bit not set in 
the value, you get a N, separated by comma.
 
To convert integer 18 to binary value representation, use BIN 
function. So SELECT BIN(18) is 10010. Then 10010 is examined 
from right to left. 
 
Y or N is added to the result in this fashion: 
 
(1) The length of the result is 10. That is, 
    10010 is 0000010010 for the length of 10.
(2) 0000010010 is examined from right to left.
(2) For every 1 bit, add Y. For 0 bit, add N. 
(3) Add Y or N from left to right. 
(4) Separated each Y or N by comma.
 
The result is N,Y,N,N,Y,N,N,N,N,N
*/
SELECT EXPORT_SET(18,'Y','N',',',10);

Query result set - 1 row returned:
Result of EXPORT_SET function

EXPORT_SET function needs binary value for integer 18 (the decimal equivalent) for length 10:

Sequence Number (From righ to left)	10	9	8	7	6	5	4	3	2	1
Binary value of integer 18	0	0	0	0	0	1	0	0	1	0

Practice #11-1: Using FIELD() string function

/*
This query returns the index position 
of the string pig.
*/
SELECT FIELD('pig', 'dog', 'cat', 'pig') AS Index_Position;

Query result set - 1 row returned:
Using FIELD() string function

Practice #11-2: Using FIELD() string function to return the first matched string

/*
FIELD() only returns the position of the first 
matched string.
 
The string list ('dog', 'cat', 'pig', 'cat')
contains two strings for cat. FIELD() returns 
the index position of the first cat found,
searching from left to right.
*/
SELECT FIELD('cat', 'dog', 'cat', 'pig', 'cat') 
AS Index_Position;

Query result set - 1 row returned:
Using FIELD() string function to return the first matched string

Practice #11-3: Using FIELD() string function to find a NULL value

/*
If we search for NULL value in the list,
FIELD() returns 0 because NULL fails equality 
comparison with any value in the list.
*/
SELECT FIELD(NULL, 'dog', 'cat', 'pig') AS Index_Position;

Query result set - 1 row returned:
Using FIELD() string function to find a NULL value

Practice #11-4: Using FIELD() and ELT() in one SELECT statement

/*
FIELD() and ELT() complement each other.
 
In this query, ELT(2, 'dog', 'cat', 'pig') returns
the string at index position 2 which is cat. And then
cat is used in FIELD() function to return the position
number of cat in argument list ('I', 'have', 'a', 'cat'),
which returns 4.
*/
SELECT FIELD(ELT(2, 'dog', 'cat', 'pig'), 'I', 'have', 'a', 'cat') 
AS Index_Position;

Query result set - 1 row returned:
Using FIELD() and ELT() in one SELECT statement

Practice #12-1: Using FIND_IN_SET() string function in SELECT statement

/*
This query searches for the index position
number of string cat in CSV list 'dog,cat,pig'
*/
SELECT FIND_IN_SET('cat','dog,cat,pig');

Query result set - 1 row returned:
Using FIND_IN_SET() in a SELECT statement

Practice #12-2: Using FIND_IN_SET() with NULL value

/*
If either argument is NULL, the function
returns NULL.
*/
SELECT FIND_IN_SET(NULL,'dog,cat,pig');
 
SELECT FIND_IN_SET('dog',NULL);

Query result set - 1 row returned:
Using FIND_IN_SET() with NULL value in a SELECT statement

Practice #13-1: Using FORMAT() string function

/*
Format a number to a format like '#,###,###.##'
 
This query formats the number to 3 decimal places.
*/
SELECT FORMAT(123567.567123, 3);

Query result set - 1 row returned:
Using FORMAT() in a SELECT statement

Practice #13-2: Using FORMAT() string function and rounds up to the nearest integer number

/*
FORMAT function also rounds up the 
last digit of the result to its
nearest integer number.
 
This query formats 123567.235 to 123,567.24
*/
SELECT FORMAT(123567.235, 2);

Query result set - 1 row returned:
Using FIND_IN_SET() with NULL value in a SELECT statement

Practice #14-1: Using HEX string function for number value

-- Returns the hexadecimal value for number 255
SELECT HEX(255);

Query result set - 1 row returned:
Using HEX string function for number value

Practice #14-2: Using HEX string function for string value

/*
If the argument in HEX() function is a string,
each character in the string is converted to 
two hexadecimal digits.
 
Returns the hexadecimal value for string Geeks
*/
SELECT HEX('Geeks');

Query result set - 1 row returned:
Using HEX string function for string value

Here is the corresponding two digits hexadecimal value for each character in the query argument showing above. Refer to the list of ASCII values for printable characters for more information.

Character	Hexadecimal
G	47
e	65
e	65
k	6B
s	73

Practice #15-1: Using INSERT() string function

/*
INSERT(original_str,pos,len,replace_str)
 
Insert the string SUPER into string Knowledgeable.
 
This query finds the character from position 5 up to
6 characters long and we get ledgea. Then replace
ledgea with string SUPER => KnowSUPERble
*/
SELECT INSERT('Knowledgeable', 5, 6, 'SUPER');

Query result set - 1 row returned:
Using INSERT() string function

Character index position for string Knowledgeable:

1	2	3	4	5	6	7	8	9	10	11	12	13
K	n	o	w	l	e	d	g	e	a	b	l	e

Practice #15-2: Using INSERT() string function when pos number is not inside the original string

/*
INSERT(original_str,pos,len,replace_str)
 
Returns the original string if position is not 
within the length of the string.
 
Position 30 is not within the length of the string,
so this query returns the original string Knowledgeable
*/
SELECT INSERT('Knowledgeable', 30, 6, 'SUPER');

Query result set - 1 row returned:
Using INSERT() string function when pos number is not inside the original string

Practice #15-3: Using INSERT() function when when len is not within the rest of the original string

/*
INSERT(original_str,pos,len,replace_str)
 
Replaces the rest of the string from position pos 
if len is not within the length of the rest of the string. 
 
Counting from position 3, the length of the rest of the 
string is 11. Len 60 is greater than 11. So this 
query replaces the rest of the string with string
SUPER.
*/
SELECT INSERT('Knowledgeable', 5, 60, 'SUPER');

Query result set - 1 row returned:
Using INSERT() string function when len is not within the rest of the original string

Other tutorials in this category

1. Using String Functions, Part 2

2. Using String Functions, Part 3

3. Using MySQL Date and Time Functions, Part 1

4. Using MySQL Date and Time Functions, Part 2

5. Using MySQL Date and Time Functions, Part 3

6. Using MySQL Control Flow Functions

7. Using MySQL Conversion Functions

8. Using MySQL Comparison Functions

9. Using Aggregate Functions in MySQL

Back to Tutorial Index Page

	Custom Search