6.3.2 String Functions

String-valued functions return NULL if the length of the result would be greater than the max_allowed_packet server parameter. See section 5.5.2 Tuning Server Parameters.

For functions that operate on string positions, the first position is numbered 1.

ASCII(str)

Returns the ASCII code value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL:

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

See also the ORD() function.

ORD(str)

If the leftmost character of the string str is a multi-byte character, returns the code for that character, calculated from the ASCII code values of its constituent characters using this formula: ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]. If the leftmost character is not a multi-byte character, returns the same value that the ASCII() function does:

mysql> SELECT ORD('2');
        -> 50

CONV(N,from_base,to_base)

Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV works with 64-bit precision:

mysql> SELECT CONV("a",16,2);
        -> '1010'
mysql> SELECT CONV("6E",18,8);
        -> '172'
mysql> SELECT CONV(-17,10,-18);
        -> '-H'
mysql> SELECT CONV(10+"10"+'10'+0xa,10,10);
        -> '40'

BIN(N)

Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL:

mysql> SELECT BIN(12);
        -> '1100'

OCT(N)

Returns a string representation of the octal value of N, where N is a longlong number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL:

mysql> SELECT OCT(12);
        -> '14'

HEX(N_or_S)

If N_OR_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16). If N_OR_S is a string, returns a hexadecimal string of N_OR_S where each character in N_OR_S is converted to 2 hexadecimal digits. This is the invers of the 0xff strings.

mysql> SELECT HEX(255);
        -> 'FF'
mysql> SELECT HEX("abc");
        -> 616263
mysql> SELECT 0x616263;
        -> "abc"

CHAR(N,...)

CHAR() interprets the arguments as integers and returns a string consisting of the characters given by the ASCII code values of those integers. NULL values are skipped:

mysql> SELECT CHAR(77,121,83,81,'76');
        -> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
        -> 'MMM'

CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have more than 2 arguments. A numeric argument is converted to the equivalent string form:

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

CONCAT_WS(separator, str1, str2,...)

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULLs and empty strings, after the separator argument. The separator will be added between the strings to be concatenated:

mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
       -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
       -> 'First name,Last Name'

LENGTH(str)

OCTET_LENGTH(str)

CHAR_LENGTH(str)

CHARACTER_LENGTH(str)

Returns the length of the string str:

mysql> SELECT LENGTH('text');
        -> 4
mysql> SELECT OCTET_LENGTH('text');
        -> 4

Note that for CHAR_LENGTH() and CHARACTER_LENGTH(), multi-byte characters are only counted once.

BIT_LENGTH(str)

Returns the length of the string str in bits:

mysql> SELECT BIT_LENGTH('text');
        -> 32

LOCATE(substr,str)

POSITION(substr IN str)

Returns the position of the first occurrence of substring substr in string str. Returns 0 if substr is not in str:

mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0

This function is multi-byte safe. In MySQL 3.23 this function is case sensitive, while in 4.0 it's only case-sensitive if either argument is a binary string.

LOCATE(substr,str,pos)

Returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str:

mysql> SELECT LOCATE('bar', 'foobarbar',5);
        -> 7

This function is multi-byte safe. In MySQL 3.23 this function is case sensitive, while in 4.0 it's only case-sensitive if either argument is a binary string.

INSTR(str,substr)

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the arguments are swapped:

mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0

This function is multi-byte safe. In MySQL 3.23 this function is case sensitive, while in 4.0 it's only case-sensitive if either argument is a binary string.

LPAD(str,len,padstr)

Returns the string str, left-padded with the string padstr until str is len characters long. If str is longer than len' then it will be shortened to len characters.

mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'

RPAD(str,len,padstr)

Returns the string str, right-padded with the string padstr until str is len characters long. If str is longer than len' then it will be shortened to len characters.

mysql> SELECT RPAD('hi',5,'?');
        -> 'hi???'

LEFT(str,len)

Returns the leftmost len characters from the string str:

mysql> SELECT LEFT('foobarbar', 5);
        -> 'fooba'

This function is multi-byte safe.

RIGHT(str,len)

Returns the rightmost len characters from the string str:

mysql> SELECT RIGHT('foobarbar', 4);
        -> 'rbar'

This function is multi-byte safe.

SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len)

MID(str,pos,len)

Returns a substring len characters long from string str, starting at position pos. The variant form that uses FROM is SQL-92 syntax:

mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'

This function is multi-byte safe.

SUBSTRING(str,pos)

SUBSTRING(str FROM pos)

Returns a substring from string str starting at position pos:

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'

This function is multi-byte safe.

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned:

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

This function is multi-byte safe.

LTRIM(str)

Returns the string str with leading space characters removed:

mysql> SELECT LTRIM('  barbar');
        -> 'barbar'

RTRIM(str)

Returns the string str with trailing space characters removed:

mysql> SELECT RTRIM('barbar   ');
        -> 'barbar'

This function is multi-byte safe.

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)

Returns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers BOTH, LEADING or TRAILING are given, BOTH is assumed. If remstr is not specified, 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'

This function is multi-byte safe.

SOUNDEX(str)

Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is 4 characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphanumeric characters are ignored in the given string. All international alpha characters outside the A-Z range are treated as vowels:

mysql> SELECT SOUNDEX('Hello');
        -> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
        -> 'Q36324'

SPACE(N)

Returns a string consisting of N space characters:

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

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str:

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

This function is multi-byte safe.

REPEAT(str,count)

Returns a string consisting of the string str repeated count times. If count <= 0, returns an empty string. Returns NULL if str or count are NULL:

mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL'

REVERSE(str)

Returns the string str with the order of the characters reversed:

mysql> SELECT REVERSE('abc');
        -> 'cba'

This function is multi-byte safe.

INSERT(str,pos,len,newstr)

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr:

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'

This function is multi-byte safe.

ELT(N,str1,str2,str3,...)

Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD():

mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
        -> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
        -> 'foo'

FIELD(str,str1,str2,str3,...)

Returns the index of str in the str1, str2, str3, ... list. Returns 0 if str is not found. FIELD() is the complement of ELT():

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

FIND_IN_SET(str,strlist)

Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimised to use bit arithmetic! Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a `,':

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

MAKE_SET(bits,str1,str2,...)

Returns a set (a string containing substrings separated by `,' characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, etc. NULL strings in str1, str2, ... are not appended to the result:

mysql> SELECT MAKE_SET(1,'a','b','c');
        -> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
        -> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
        -> ''

EXPORT_SET(bits,on,off,[separator,[number_of_bits]])

Returns a string where for every bit set in 'bit', you get an 'on' string and for every reset bit you get an 'off' string. Each string is separated with 'separator' (default ',') and only 'number_of_bits' (default 64) of 'bits' is used:

mysql> SELECT EXPORT_SET(5,'Y','N',',',4)
        -> Y,N,Y,N

LCASE(str)

LOWER(str)

Returns the string str with all characters changed to lowercase according to the current character set mapping (the default is ISO-8859-1 Latin1):

mysql> SELECT LCASE('QUADRATICALLY');
        -> 'quadratically'

This function is multi-byte safe.

UCASE(str)

UPPER(str)

Returns the string str with all characters changed to uppercase according to the current character set mapping (the default is ISO-8859-1 Latin1):

mysql> SELECT UCASE('Hej');
        -> 'HEJ'

This function is multi-byte safe.

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. The file must be on the server, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and be smaller than max_allowed_packet. If the file doesn't exist or can't be read due to one of the above reasons, the function returns NULL:

mysql> UPDATE tbl_name
           SET blob_column=LOAD_FILE("/tmp/picture")
           WHERE id=1;

If you are not using MySQL Version 3.23, you have to do the reading of the file inside your application and create an INSERT statement to update the database with the file information. One way to do this, if you are using the MySQL++ library, can be found at http://www.mysql.com/documentation/mysql++/mysql++-examples.html.

QUOTE(str)

Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned surrounded by single quotes and with each instance of single quote (`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word ``NULL'' without surrounding single quotes. The QUOTE function was added in MySQL version 4.0.3.

mysql> SELECT QUOTE("Don't");
        -> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
        -> NULL

Subsections

• 6.3.2.1 String Comparison Functions
• 6.3.2.2 Case-Sensitivity

User Comments

It appears that something like this does not work:\n

SELECT CASE c.flag_user WHEN 0 THEN 0 WHEN 1 THEN 2 ELSE 1 END AS myflag\n
FROM bi_clones c, bi_hits h WHERE h.clone = c.id ORDER BY c.name LIMIT 0, 50
\n
In this example, myflag will be 0 even if flag_user is 1. However, when there is no join,
the query works as expected. I.e.
\n
SELECT CASE c.flag_user WHEN 0 THEN 0 WHEN 1 THEN 2 ELSE 1 END AS myflag\n
FROM bi_clones c ORDER BY c.name LIMIT 0, 50
\n
produces the same result as
\n
SELECT IF(c.flag_user,IF(c.flag_user-1,1,2),0) AS myflag\n
FROM bi_clones c ORDER BY c.name LIMIT 0, 50
\n
The manual doesn't state the CASE only works for SELECTS on one
table, so I suppose I might have stumbled upon a bug of some sort.
Luckily it's easy to replace CASE with IF() wherever needed.

how could I emulate the XOR operator with abiable
logical operators in mysql?,
i've to do something like this:
select * from table1,table2 where (table1.a) xor
(table1.a=table2.a);
this've to work like minus operator..
thanks in advance
..

the min and max arguments to BETWEEN mean it.
contrary to english usage,
select 5 between 4 and 6 ; (1) is not equal to
select 5 between 6 and 4 ; (0)
the optimizer neither sorts the operands nor
catches the always-false case.
mysql> select id from speed where stamp between 6
and 4 ;
Empty set (28.51 sec)
(3 million records in table, server version
3.23.42-nt)

I was confused with the same problem: how to
return rows that may or may not have a related
record in another table.
Then I learned about left joins. A left join is
basically an XOR if statement. Look on the join
doc page for examples.
This one worked for me:
mysql> select * from table1 LEFT JOIN table2 ON
table1.id=table2.id;

is the file for LOAD_FILE loaded from the client
or the server?

is LOAD_FILE replication safe?

ASP users: if you're getting empty recordset
returned when using IFNULL, add "OPTION=16384" to
your connectionstring, or check "Change Bigint to
Int" in the DSN manager!

a XOR b can also be accomplished as:

(a AND NOT b) OR (b AND NOT a)

(table1.a) xor (table1.a=table2.a) becomes:

(table1.a AND NOT (table1.a=table2.a)) OR
((table1.a=table2.a) AND NOT table1.a)

Warning:

locate('',anything) always returns 1

nice feature would be something that will work as
SUM(), but on text results. something like join
(',', $array) in PHP. The result will be a joined
set, with and delimitter of text results.
i tried CONCAT and CONCAT_WS, but does not work
the way i want. or maybe i do something wrong

I found a simple little way of converting a string to
an int in version 3.23. CONV(N, 10, 10) where N is
the
string. I had to use this because when i tried to
return SUM(varchar_column), it would funkify the
data. the string '1' got converted
to '1.00000071246087'. SUM(CONV
(varchar_column, 10, 10)) seems to work fine
though. it leaves '1' as '1'.

Perhaps I misunderstand how load_file works,
but the following does not seem right:

mysql> select load_file('/home/alex/.bashrc')
from test;
+-----------------------------------+
| load_file('/home/alex/.bashrc') |
+-----------------------------------+
| NULL |
+-----------------------------------+
1 row in set (0.00 sec)

Inserting an existing file into a BLOB field using
load_file() function does not work either.

If you are having trouble uploading a file via PHP into a MySQL database, you must use PHP's chmod command to change the permissions on the uploaded file. For example:
chmod($_FILES['myfield']['tmp_name'],0644);
This will allow the mysqld user (the unix user of the mysql daemon process, not the mysql user) to read the file. Also don't forget to set the FILE permission on the mysql user.

Where is INSTRREV / LASTINDEXOF?

It seems the function to search a string starting from the end is missing. In VB/ASP, it's called InStrRev, in javascript it's lastIndexOf.

You can fake it by doing this:

SELECT LENGTH("The string to, search!") - INSTR(REVERSE("The string to, search!"), ",") + 1

but this is hardly practical. Ideally:

InStrRev(STRING_TO_SEARCH, SEARCH_FOR, START_AT_POSITION)

Where leaving out START_AT_POSITION or specifying -1 will begin at the end of the string and search towards the beginning.

to take off quote from string at the begining and at the end of it, you should do the this query:

update <table> set <attrib>= substring(<attrib>,2,length(<attrib>)-2);

"some text type attrib" (before)

some text type attrib (after / without quote)

I think it would be nice to have a ucfirst function that would make the first character of a string upper case or lcfirst to do the inverse. Instead you have to do something like this to make your text a proper name (in 3.23.23):


concat(upper(substring(string,1,1)), substring(string,2))

It'll be also nice to have something like ucwords() to capitalize first character of each word in string.

Example of "order by" on a varchar column containing IP Addresses:
10.144.203.1
10.144.203.10
10.144.203.2

Example of How to order properly:

select IP from SomeTable order by lpad(mid(IP,locate('.',IP,locate('.',IP,locate('.',IP)+1)+1)+1,4),3,0);

Output:
10.144.203.1
10.144.203.2
10.144.203.10

One gripe I have is with the locate() function... Anyone ever consider adding an "nth" occurrance feature? Would make the above alot simpler - you could just specify locate('.',IP,n) where n would be the 3rd occurrence of the dot rather than n being the starting position in the string/field.

Probably something good that will allow simple XML use with low network load and low clients' overhead, is to have a REPLACE function with regexp capabilities like the following:
REPLACEREGEXP(sourcestring,REGEXP condition,replacestring)
where the sourcestring can be a field

Order by ip

order by inet_aton(ip);

SUBSTRING_INDEX is case sensitive. This should be in the manual.

Don't get me wrong, I also agree on the REGEXP function, but for now you can use nested REPLACE's:

SELECT title REPLACE(REPLACE(REPLACE(title, 'A ', ''), 'An ',''), 'The ', '') as sortedtitle FROM books WHERE 1

or beter use TRIM with this example, cause you only want these words to be remove from the front:

SELECT title TRIM(LEADING 'A ' FROM TRIM(LEADING 'An ' FROM TRIM(LEADING 'The ' FROM title))) as sortedtitle FROM books WHERE 1

Nice feature would be something that will work as || for Oracle or + in SQL Server to concatenate two strings.

Add your own comment.