String

Numeric Conversion

ASCII(str) returns the numeric code of the leftmost character of str. ORD(str) is similar but also works for multi-byte characters, computing a numeric value from all the bytes involved.

SELECT ASCII('abc');

97 — the code for 'a'.

SELECT ASCII('abc');

SELECT ASCII('abc');

ASCII('abc')
97

CHAR(N1,N2,...) interprets each numeric argument as a character code and returns the resulting string. BIN(N), OCT(N), and HEX(N) return a string representation of N in base 2, 8, and 16 respectively. UNHEX(str) is the inverse of HEX(), interpreting pairs of hex digits as byte values.

SELECT CHAR(77,121,83,81,'76');
SELECT BIN(13), OCT(51), HEX(47);
SELECT UNHEX('4D7953514C'), 0x4D7953514C;

'MySQL' (77='M', 121='y', 83='S', 81='Q', and the string '76' is itself interpreted as the number 76='L'); '1101', '63', '2F'; 'MySQL', 'MySQL' — a bare hex literal like 0x4D7953514C is equivalent to UNHEX() on its digits.

FORMAT(x, D) rounds x to D decimal places and formats the result as a string with a thousands separator.

SELECT FORMAT(123456.123456, 4);

'123,456.1235'

SELECT CHAR(77,121,83,81,'76');

SELECT BIN(13), OCT(51), HEX(47);

SELECT UNHEX('4D7953514C'), 0x4D7953514C;

SELECT FORMAT(123456.123456, 4);

SELECT CHAR(77,121,83,81,'76');

SELECT BIN(13), OCT(51), HEX(47);

SELECT UNHEX('4D7953514C'), 0x4D7953514C;

SELECT FORMAT(123456.123456, 4);

SELECT CHAR(77,121,83,81,'76'):

CHAR(77,121,83,81,'76')
MySQL

SELECT BIN(13), OCT(51), HEX(47):

BIN(13)	OCT(51)	HEX(47)
1101	63	2F

SELECT UNHEX('4D7953514C'), 0x4D7953514C:

UNHEX('4D7953514C')	0x4D7953514C
MySQL	MySQL

SELECT FORMAT(123456.123456, 4):

FORMAT(123456.123456, 4)
123,456.1235

Length

LENGTH(str) and OCTET_LENGTH(str) return the length of str in bytes. BIT_LENGTH(str) returns the length in bits. CHAR_LENGTH(str) and CHARACTER_LENGTH(str) (synonyms) return the length in characters, which can differ from LENGTH() for a multi-byte character set. WEIGHT_STRING(str) returns the sort weight MySQL would use to compare str under the current collation, useful (via HEX()) for debugging collation-sensitive comparisons.

SELECT LENGTH('abc '), CHAR_LENGTH('abc ');

4, 4 — identical here because 'abc ' (with a trailing space) is single-byte ASCII; they can diverge for multi-byte text.

SELECT LENGTH('abc '), CHAR_LENGTH('abc ');

SELECT LENGTH('abc '), CHAR_LENGTH('abc ');

LENGTH('abc ')	CHAR_LENGTH('abc ')
4	4

Comparison

STRCMP(str1, str2) returns -1, 0, or 1 depending on whether str1 sorts before, equal to, or after str2.

SELECT STRCMP('abc','def'), STRCMP('def','abc'), STRCMP('abc','abc');
SELECT STRCMP('abc','abcd');

-1, 1, 0; -1 — 'abc' precedes 'abcd' since it is a strict prefix.

SELECT STRCMP('abc','def'), STRCMP('def','abc'), STRCMP('abc','abc');

SELECT STRCMP('abc','abcd');

SELECT STRCMP('abc','def'), STRCMP('def','abc'), STRCMP('abc','abc');

SELECT STRCMP('abc','abcd');

SELECT STRCMP('abc','def'), STRCMP('def','abc'), STRCMP('abc','abc'):

STRCMP('abc','def')	STRCMP('def','abc')	STRCMP('abc','abc')
-1	1	0

SELECT STRCMP('abc','abcd'):

STRCMP('abc','abcd')
-1

Case

UCASE(str) and UPPER(str) are synonyms that return str in uppercase; LCASE(str) and LOWER(str) are synonyms that return str in lowercase.

SELECT UCASE('aBcd'), UPPER('EFgh');
SELECT LCASE('aBcd'), LOWER('EFgh');

'ABCD', 'EFGH'; 'abcd', 'efgh'

SELECT UCASE('aBcd'), UPPER('EFgh');

SELECT LCASE('aBcd'), LOWER('EFgh');

SELECT UCASE('aBcd'), UPPER('EFgh');

SELECT LCASE('aBcd'), LOWER('EFgh');

SELECT UCASE('aBcd'), UPPER('EFgh'):

UCASE('aBcd')	UPPER('EFgh')
ABCD	EFGH

SELECT LCASE('aBcd'), LOWER('EFgh'):

LCASE('aBcd')	LOWER('EFgh')
abcd	efgh

Spacing and Padding

SPACE(N) returns a string of N spaces. TRIM(str) removes leading and trailing spaces; LTRIM(str) and RTRIM(str) remove only leading, respectively trailing, spaces. LPAD(str, len, padstr) and RPAD(str, len, padstr) pad (or truncate) str to length len using padstr on the left, respectively right.

SELECT LPAD('abcd',7,'--');
SELECT RPAD('abcd',3,'--');

'---abcd'; 'abc' — when len is shorter than str, the result is truncated rather than padded.

SELECT LPAD('abcd',7,'--');

SELECT RPAD('abcd',3,'--');

SELECT LPAD('abcd',7,'--');

SELECT RPAD('abcd',3,'--');

SELECT LPAD('abcd',7,'--'):

LPAD('abcd',7,'--')
---abcd

SELECT RPAD('abcd',3,'--'):

RPAD('abcd',3,'--')
abc

Concatenation

CONCAT(str1, str2, ...) joins its arguments into one string, returning NULL if any argument is NULL. CONCAT_WS(sep, str1, str2, ...) joins with a separator, silently skipping NULL arguments instead of propagating NULL. REPEAT(str, count) repeats str count times.

SELECT CONCAT('ab',12,'cd'), CONCAT('ab',12,NULL);
SELECT CONCAT_WS(',','ab',12,'cd'), CONCAT_WS(',','ab',12,NULL);
SELECT REPEAT('abc',3);

'ab12cd', NULL; 'ab,12,cd', 'ab,12'; 'abcabcabc'

SELECT CONCAT('ab',12,'cd'), CONCAT('ab',12,NULL);

SELECT CONCAT_WS(',','ab',12,'cd'), CONCAT_WS(',','ab',12,NULL);

SELECT REPEAT('abc',3);

SELECT CONCAT('ab',12,'cd'), CONCAT('ab',12,NULL);

SELECT CONCAT_WS(',','ab',12,'cd'), CONCAT_WS(',','ab',12,NULL);

SELECT REPEAT('abc',3);

SELECT CONCAT('ab',12,'cd'), CONCAT('ab',12,NULL):

CONCAT('ab',12,'cd')	CONCAT('ab',12,NULL)
ab12cd	NULL

SELECT CONCAT_WS(',','ab',12,'cd'), CONCAT_WS(',','ab',12,NULL):

CONCAT_WS(',','ab',12,'cd')	CONCAT_WS(',','ab',12,NULL)
ab,12,cd	ab,12

SELECT REPEAT('abc',3):

REPEAT('abc',3)
abcabcabc

A session variable can accumulate a running concatenation across rows:

SET @tokens = "";
SELECT @tokens := CONCAT(token, ',', @tokens) FROM T;
SELECT @tokens;

Combined with an incrementing variable, REPEAT() can also generate a simple ASCII bar chart across rows:

SET @a := 0;
SELECT REPEAT('* ', @a := @a + 1)
FROM INFORMATION_SCHEMA.TABLES
WHERE @a < 10;

Produces 10 rows: "*", "**", "***", ... up through 10 asterisks.

Working with a Fixed Set of Arguments

ELT(N, str1, str2, ...) returns the N-th string argument. EXPORT_SET(bits, on, off [, sep [, N]]) returns a string representing bits as a sequence of on/off strings joined by sep, one per bit (0 through N-1, default 64). MAKE_SET(bits, str1, str2, ...) returns a comma-joined subset of the string arguments whose corresponding bit position is set in bits. FIELD(str, str1, str2, ...) returns the 1-based index of str among the remaining arguments, or 0 if not found. FIND_IN_SET(str, strlist) returns the 1-based position of str within a comma-separated strlist.

SELECT ELT(2,'ab','cd','ef');
SELECT EXPORT_SET(6,'1','0','|',8);
SELECT MAKE_SET(5,'ab','cd','ef');
SELECT FIELD('cd','ab','cd','ef','gh');
SELECT FIND_IN_SET('cd','ab,cd,ef,gh');

'cd'; '0|1|1|0|0|0|0|0' (6 is binary 00000110, low bit first); 'ab,ef' (5 is binary 101, so bit 0 and bit 2 select 'ab' and 'ef'); 2; 2

SELECT ELT(2,'ab','cd','ef');

SELECT EXPORT_SET(6,'1','0','|',8);

SELECT MAKE_SET(5,'ab','cd','ef');

SELECT FIELD('cd','ab','cd','ef','gh');

SELECT FIND_IN_SET('cd','ab,cd,ef,gh');

SELECT ELT(2,'ab','cd','ef');

SELECT EXPORT_SET(6,'1','0','|',8);

SELECT MAKE_SET(5,'ab','cd','ef');

SELECT FIELD('cd','ab','cd','ef','gh');

SELECT FIND_IN_SET('cd','ab,cd,ef,gh');

SELECT ELT(2,'ab','cd','ef'):

ELT(2,'ab','cd','ef')
cd

SELECT EXPORT_SET(6,'1','0','|',8):

EXPORT_SET(6,'1','0','\|',8)
0\|1\|1\|0\|0\|0\|0\|0

SELECT MAKE_SET(5,'ab','cd','ef'):

MAKE_SET(5,'ab','cd','ef')
ab,ef

SELECT FIELD('cd','ab','cd','ef','gh'):

FIELD('cd','ab','cd','ef','gh')
2

SELECT FIND_IN_SET('cd','ab,cd,ef,gh'):

FIND_IN_SET('cd','ab,cd,ef,gh')
2

Substring

LOCATE(substr, str [, pos]) (synonym: POSITION()) returns the 1-based position of substr within str, searching from pos, or 0 if not found. INSTR(str, substr) is similar but always searches from the start. LEFT(str, len) and RIGHT(str, len) return the leftmost/rightmost len characters. SUBSTR(str, pos [, len]) (synonym: SUBSTRING()) and its FROM pos [FOR len] spelling extract a substring starting at pos (or, if pos is negative, that many characters from the end). INSERT(str, pos, len, newstr) replaces len characters of str starting at pos with newstr. REPLACE(str, from, to) replaces every occurrence of from with to. REVERSE(str) reverses str. SUBSTRING_INDEX(str, delim, count) returns everything before the count-th occurrence of delim (or, for a negative count, everything after the count-th occurrence counting from the end).

SELECT LOCATE('cd','abcdef');
SELECT LOCATE('cd','abcdefcd',4), LOCATE('zz','abcdefcd',4);
SELECT INSTR('abcdef','cd');
SELECT LEFT('abcdef',3), RIGHT('abcdef',3);
SELECT SUBSTR('abcdef',3), SUBSTR('abcdef' FROM 3);
SELECT SUBSTR('abcdef',3,2), SUBSTR('abcdef' FROM 3 FOR 2);
SELECT SUBSTR('abcdef',-2);
SELECT INSERT('abcdef',3,2,'XXXX');
SELECT REPLACE('abcdabef','ab','XX');
SELECT REVERSE('abcd');
SELECT SUBSTRING_INDEX('www.google.com','.',2);
SELECT SUBSTRING_INDEX('www.google.com','.',-2);

3; 7, 0; 3; 'abc', 'def'; 'cdef', 'cdef'; 'cd', 'cd'; 'ef'; 'abXXXXef'; 'XXcdXXef'; 'dcba'; 'www.google'; 'google.com'

SELECT LOCATE('cd','abcdef');

SELECT LOCATE('cd','abcdefcd',4), LOCATE('zz','abcdefcd',4);

SELECT INSTR('abcdef','cd');

SELECT LEFT('abcdef',3), RIGHT('abcdef',3);

SELECT SUBSTR('abcdef',3), SUBSTR('abcdef' FROM 3);

SELECT SUBSTR('abcdef',3,2), SUBSTR('abcdef' FROM 3 FOR 2);

SELECT SUBSTR('abcdef',-2);

SELECT INSERT('abcdef',3,2,'XXXX');

SELECT REPLACE('abcdabef','ab','XX');

SELECT REVERSE('abcd');

SELECT SUBSTRING_INDEX('www.google.com','.',2);

SELECT SUBSTRING_INDEX('www.google.com','.',-2);

SELECT LOCATE('cd','abcdef');

SELECT LOCATE('cd','abcdefcd',4), LOCATE('zz','abcdefcd',4);

SELECT INSTR('abcdef','cd');

SELECT LEFT('abcdef',3), RIGHT('abcdef',3);

SELECT SUBSTR('abcdef',3), SUBSTR('abcdef' FROM 3);

SELECT SUBSTR('abcdef',3,2), SUBSTR('abcdef' FROM 3 FOR 2);

SELECT SUBSTR('abcdef',-2);

SELECT INSERT('abcdef',3,2,'XXXX');

SELECT REPLACE('abcdabef','ab','XX');

SELECT REVERSE('abcd');

SELECT SUBSTRING_INDEX('www.google.com','.',2);

SELECT SUBSTRING_INDEX('www.google.com','.',-2);

SELECT LOCATE('cd','abcdef'):

LOCATE('cd','abcdef')
3

SELECT LOCATE('cd','abcdefcd',4), LOCATE('zz','abcdefcd',4):

LOCATE('cd','abcdefcd',4)	LOCATE('zz','abcdefcd',4)
7	0

SELECT INSTR('abcdef','cd'):

INSTR('abcdef','cd')
3

SELECT LEFT('abcdef',3), RIGHT('abcdef',3):

LEFT('abcdef',3)	RIGHT('abcdef',3)
abc	def

SELECT SUBSTR('abcdef',3), SUBSTR('abcdef' FROM 3):

SUBSTR('abcdef',3)	SUBSTR('abcdef' FROM 3)
cdef	cdef

SELECT SUBSTR('abcdef',3,2), SUBSTR('abcdef' FROM 3 FOR 2):

SUBSTR('abcdef',3,2)	SUBSTR('abcdef' FROM 3 FOR 2)
cd	cd

SELECT SUBSTR('abcdef',-2):

SUBSTR('abcdef',-2)
ef

SELECT INSERT('abcdef',3,2,'XXXX'):

INSERT('abcdef',3,2,'XXXX')
abXXXXef

SELECT REPLACE('abcdabef','ab','XX'):

REPLACE('abcdabef','ab','XX')
XXcdXXef

SELECT REVERSE('abcd'):

REVERSE('abcd')
dcba

SELECT SUBSTRING_INDEX('www.google.com','.',2):

SUBSTRING_INDEX('www.google.com','.',2)
www.google

SELECT SUBSTRING_INDEX('www.google.com','.',-2):

SUBSTRING_INDEX('www.google.com','.',-2)
google.com

Miscellaneous

QUOTE(str) returns str wrapped in single quotes, with any single quote, backslash, NUL, or Control-Z inside it escaped so the result is safe to use as an SQL string literal. SOUNDEX(str) returns a phonetic code for str; two strings that sound alike (even if spelled differently) tend to produce the same code. str1 SOUNDS LIKE str2 is shorthand for comparing their SOUNDEX() codes. TO_BASE64(str) and FROM_BASE64(str) encode/decode base64. LOAD_FILE(file_name) reads the named file (subject to the FILE privilege and the secure_file_priv variable) and returns its contents, or NULL if it cannot be read.

SELECT QUOTE("O'Reilly");
SELECT SOUNDEX('hello'), SOUNDEX('helo'), 'hello' SOUNDS LIKE 'helo';
SELECT TO_BASE64('xyz'), FROM_BASE64(TO_BASE64('xyz'));

"'O\'Reilly'"; both SOUNDEX() calls return the same code (e.g. 'H400'), so SOUNDS LIKE evaluates to 1; 'eHl6', 'xyz'

SELECT QUOTE("O'Reilly");

SELECT SOUNDEX('hello'), SOUNDEX('helo'), 'hello' SOUNDS LIKE 'helo';

SELECT TO_BASE64('xyz'), FROM_BASE64(TO_BASE64('xyz'));

SELECT QUOTE("O'Reilly");

SELECT SOUNDEX('hello'), SOUNDEX('helo'), 'hello' SOUNDS LIKE 'helo';

SELECT TO_BASE64('xyz'), FROM_BASE64(TO_BASE64('xyz'));

SELECT QUOTE("O'Reilly"):

QUOTE("O'Reilly")
'O\'Reilly'

SELECT SOUNDEX('hello'), SOUNDEX('helo'), 'hello' SOUNDS LIKE 'helo':

SOUNDEX('hello')	SOUNDEX('helo')	'hello' SOUNDS LIKE 'helo'
H400	H400	1

SELECT TO_BASE64('xyz'), FROM_BASE64(TO_BASE64('xyz')):

TO_BASE64('xyz')	FROM_BASE64(TO_BASE64('xyz'))
eHl6	xyz

MENU