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:
- Part 2 of MySQL String Functions
- Part 3 of MySQL String Functions
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.
You can concatenate a string with one or more columns from a table, see Practice #7-2.
If an argument is numeric number, it is automatically converted to a string. See Practice #7-3.
CONCAT() returns NULL if any argument is NULL. See Practice #7-4.
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
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT ProductName,
LEFT(ProductName,1) AS "First Letter",
ASCII(ProductName) AS "First Letter ASCII"
FROM products;
Query result set - 77 rows returned:
Practice #2: Using BIN string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT BIN(23764) AS "Binary value";
Query result set - 1 row returned:
Practice #3: Using BIT_LENGTH string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT BIT_LENGTH('Geeks');
Query result set - 1 row returned:
Practice #4-1: Using CHAR_LENGTH string function for English characters (1-byte)
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT CHAR_LENGTH('Geeks');
Query result set - 1 row returned:
Practice #4-2: Using CHAR_LENGTH string function for multi-byte characters
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT CHAR_LENGTH('汉字');
Query result set - 1 row returned:
Practice #5: Using CHAR() string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT CHAR(71,101,101,107,115,69,110,103,105,110,101);
Query result set - 1 row returned:
Practice #7-1: Using CONCAT() string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT CONCAT('Geeks', 'Engine', '.com');
Query result set - 1 row returned:
Practice #7-2: Using CONCAT() string function to concatenate a string with a column
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT CONCAT('This is ', CategoryName, ' category.') as description FROM categories
Query result set - 8 rows returned:
Practice #7-3: Using CONCAT() string function for numeric values
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT CONCAT('MySQL ', 5.1, ' has more functions than 5.0 release.');
Query result set - 1 row returned:
Practice #7-4: Using CONCAT() string function for NULL values
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT CONCAT('Web', 'site', NULL);
Query result set - 1 row returned:
Practice #8-1: Using CONCAT_WS() string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT CONCAT_WS(',', 'music', 'outdoors', 'movies');
Query result set - 1 row returned:
Practice #8-2: Using CONCAT_WS() string function and use NULL as the separator
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT CONCAT_WS(NULL, 'music', 'outdoors', 'movies');
Query result set - 1 row returned:
Practice #9-1: Using ELT() string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT ELT(2, 'music', 'outdoors', 'movies');
Query result set - 1 row returned:
Practice #9-2: Using ELT() string function and the position number can be rounded to one
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT ELT(0.51, 'music', 'outdoors', 'movies');
Query result set - 1 row returned:
Practice #9-3: Using ELT() string function and the position number cannot be rounded to one
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT ELT(0.49, 'music', 'outdoors', 'movies');
Query result set - 1 row returned:
Practice #9-4: Using ELT() string function and the position number is greater than the number of elements in the string list
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT ELT(4, 'music', 'outdoors', 'movies');
Query result set - 1 row returned:
Practice #10: Using EXPORT_SET string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT EXPORT_SET(18,'Y','N',',',10);
Query result set - 1 row returned:
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
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FIELD('pig', 'dog', 'cat', 'pig') AS Index_Position;
Query result set - 1 row returned:
Practice #11-2: Using FIELD() string function to return the first matched string
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FIELD('cat', 'dog', 'cat', 'pig', 'cat')
AS Index_Position;
Query result set - 1 row returned:
Practice #11-3: Using FIELD() string function to find a NULL value
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FIELD(NULL, 'dog', 'cat', 'pig') AS Index_Position;
Query result set - 1 row returned:
Practice #11-4: Using FIELD() and ELT() in one SELECT statement
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FIELD(ELT(2, 'dog', 'cat', 'pig'), 'I', 'have', 'a', 'cat')
AS Index_Position;
Query result set - 1 row returned:
Practice #12-1: Using FIND_IN_SET() string function in SELECT statement
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FIND_IN_SET('cat','dog,cat,pig');
Query result set - 1 row returned:
Practice #12-2: Using FIND_IN_SET() with NULL value
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FIND_IN_SET(NULL,'dog,cat,pig');
SELECT FIND_IN_SET('dog',NULL);
Query result set - 1 row returned:
Practice #13-1: Using FORMAT() string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FORMAT(123567.567123, 3);
Query result set - 1 row returned:
Practice #13-2: Using FORMAT() string function and rounds up to the nearest integer number
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT FORMAT(123567.235, 2);
Query result set - 1 row returned:
Practice #14-1: Using HEX string function for number value
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
Query result set - 1 row returned:
Practice #14-2: Using HEX string function for string value
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
Query result set - 1 row returned:
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
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT INSERT('Knowledgeable', 5, 6, 'SUPER');
Query result set - 1 row returned:
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
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT INSERT('Knowledgeable', 30, 6, 'SUPER');
Query result set - 1 row returned:
Practice #15-3: Using INSERT() function when when len is not within the rest of the original string
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT INSERT('Knowledgeable', 5, 60, 'SUPER');
Query result set - 1 row returned:
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
10. MySQL RANK() function
11. MySQL DENSE_RANK() function
12. MySQL ROW_NUMBER() function
13. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function
14. Using LIMIT and OFFSET to Page Thru Query Result in MySQL
15. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL
16. Use LEAST function to find the SMALLEST value from multiple arguments
17. How to make case-sensitive comparison in MySQL
18. Use GREATEST function to find the LARGEST value from multiple arguments
19. MySQL MID() function with examples
20. MySQL LOCATE() function with examples
21. MySQL SUBSTR() function with examples
22. MySQL POSITION() function with examples
23. How to use MySQL REPLACE function in SELECT and UPDATE queries
Back to Tutorial Index Page