String functions.

ASCII(S)

Returns the ASCII code value of the leftmost character of S. If S is NULL return NULL.

mysql> SELECT ascii(2);                         -> 50
mysql> SELECT ascii('dx');                      -> 100

CHAR(X,...)

Returns a string that consists of the characters given by the ASCII code values of the arguments. NULLs are skipped.

mysql> SELECT char(77,121,83,81,'76');          -> 'MySQL'

CONCAT(X,Y...)

Concatenates strings. May have more than 2 arguments.

mysql> SELECT CONCAT('My', 'S', 'QL');          -> 'MySQL'

LENGTH(S)

OCTET_LENGTH(S)

CHAR_LENGTH(S)

CHARACTER_LENGTH(S)

Length of string.

mysql> SELECT length('text');                   -> 4
mysql> SELECT octet_length('text');             -> 4

LOCATE(A,B)

POSITION(B IN A)

Returns position of A substring in B. The first position is 1. Returns 0 if A is not in B.

mysql> select locate('bar', 'foobarbar');         -> 4
mysql> select locate('xbar', 'foobar');           -> 0

INSTR(A,B)

Returns position of first substring B in string A. This is the same as LOCATE with swapped parameters.

mysql> select instr('foobarbar', 'bar');         -> 4
mysql> select instr('xbar', 'foobar');           -> 0

LOCATE(A,B,C)

Returns position of first substring A in string B starting at C.

mysql> select locate('bar', 'foobarbar',5);        -> 7

LEFT(str,length)

Gets length in characters from beginning of string.

mysql> select left('foobarbar', 5);             -> 'fooba'

RIGHT(A,B)

SUBSTRING(A FROM B)

Gets B characters from end of string A.

mysql> select right('foobarbar', 5);            -> 'arbar'
mysql> select substring('foobarbar' from 5);    -> 'arbar'

LTRIM(str)

Removes space characters from the beginning of string.

mysql> select ltrim('  barbar');                -> 'barbar'

RTRIM(str)

Removes space characters from the end of string. mysql> select rtrim('barbar '); -> 'barbar'

TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)

Returns a character string with all A prefixes and/or suffixes removed from B. If BOTH, LEADING and TRAILING isn't used BOTH are assumed. If A is not given, then spaces are removed.

mysql> select trim('  bar   ');                      -> 'bar'
mysql> select trim(leading 'x' from 'xxxbarxxx');    -> 'barxxx'
mysql> select trim(both 'x' from 'xxxbarxxx');       -> 'bar'
mysql> select trim(trailing 'xyz' from 'barxxyz');   -> 'barx'

SOUNDEX(S)

Gets a soundex string from S. Two strings that sound 'about the same' should have identical soundex strings. A 'standard' soundex string is 4 characters long, but this function returns an arbitrary long string. One can use SUBSTRING on the result to get a 'standard' soundex string. All non alpha characters are ignored in the given string. All characters outside the A-Z range are treated as vocals.

mysql> select soundex('Hello');                 -> 'H400'
mysql> select soundex('Bättre');                -> 'B360'
mysql> select soundex('Quadratically');         -> 'Q36324'

SUBSTRING(A, B, C)

SUBSTRING(A FROM B FOR C)

MID(A, B, C)

Returns substring from A starting at B with C chars. The variant with FROM is ANSI SQL 92 syntax.

mysql> select substring('Quadratically',5,6);          -> ratica

SUBSTRING_INDEX(String, Delimiter, Count)

Returns the substring from String after Count Delimiters. If Count is positive the strings are searched from left else if count is negative the substrings are searched and returned from right.

mysql> select substring_index('www.tcx.se', '.', 2);   -> 'www.tcx'
mysql> select substring_index('www.tcx.se', '.', -2);  -> 'tcx.se'

SPACE(N)

Return a string of N spaces.

mysql> select SPACE(6);         -> '      '

REPLACE(A, B, C)

Replaces all occurrences of string B in string A with string C.

mysql> select replace('www.tcx.se', 'w', 'Ww');  -> 'WwWwWw.tcx.se'

REPEAT(String, Count)

Repeats String Count times. If Count <= 0 returns a empty string. If String or Count is NULL or LENGTH(string)*count > max_allowed_size returns NULL.

mysql> select repeat('MySQL', 3);                -> 'MySQLMySQLMySQL'

REVERSE(String)

Reverses all characters in string.

mysql> select reverse('abc');  -> 'cba'

INSERT(Org, Start, Length, New)

Replaces substring in Org starging at Start and Length long with New. First position in Org is numbered 1.

mysql> select insert('Quadratic', 3, 4, 'What');   -> 'QuWhattic'

INTERVAL(N, N1, N2, N3...)

It is required that Nn > N3 > N2 > N1 is this function shall work. This is because a binary search is used (Very fast). Returns 0 if N < N1, 1 if N < N2 and so on. All arguments are treated as numbers.

mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);     -> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);           -> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);            -> 0

ELT(N, A1, A2, A3...)

Returns A1 if N = 1, A2 if N = 2 and so on. If N is less than 1 or bigger than the number of arguments NULL is returned.

mysql> select elt(1, 'ej', 'Heja', 'hej', 'foo');  -> 'ej'
mysql> select elt(4, 'ej', 'Heja', 'hej', 'foo');  -> 'foo'

FIELD(S, S1, S2, S3...)

Returns index of S in S1, S2, S3... list. The complement of ELT(). Return 0 when S is not found.

mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');       -> 2
mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');       -> 0

FIND_IN_SET(string,string of strings)

Returns a value 1 to N if the 'string' is in 'string of strings'. A 'string of strings' is a string where each different value is separated with a ','. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET is optimized to use bit arithmetic!

mysql> SELECT FIND_IN_SET('b','a,b,c,d')		-> 2

This function will not work properly if the first argument contains a ','.

LCASE(A)

LOWER(A)

Changes A to lower case according to current character set ,dmappings (Default Latin1).

mysql> select lcase('QUADRATICALLY');           -> 'quadratically'

UCASE(A)

UPPER(A)

Changes A to upper case.

mysql> select ucase('Hej');             -> 'HEJ'

Go to the first, previous, next, last section, table of contents.