|
|
String functions mostly take string arguments and return string results. Unlike most programming languages, the first character of the string is position 1, not 0.
ASCII(string)
Returns the ASCII value of the first (leftmost) character of the string, 0 if the string is empty, and NULL if the string is null.
For example:
mysql> SELECT ASCII('a'); +------------+ | ASCII('a') | +------------+ | 97 | +------------+ mysql> SELECT ASCII('aa'); +-------------+ | ASCII('az') | +-------------+ | 97 | +-------------+
Use ORD() to return the ASCII value if the character is a multibyte character.
BIN(number)
Returns the binary value (a string representation) of the specified BIGINT number, 0 if the number cannot be converted (the function will convert as far as it can from the left), and NULL if it's null.
For example:
mysql> SELECT BIN(15); +---------+ | BIN(15) | +---------+ | 1111 | +---------+ mysql> SELECT BIN('8'); +----------+ | BIN('8') | +----------+ | 1000 | +----------+ mysql> SELECT BIN('2w'); +-----------+ | BIN('2w') | +-----------+ | 10 | +-----------+ mysql> SELECT BIN('w2'); +-----------+ | BIN('w2') | +-----------+ | 0 | +-----------+
This function is equivalent to CONV(number,10,2).
BIT_LENGTH(string)
Returns the string length in bits.
For example:
mysql> SELECT BIT_LENGTH('MySQL'); +---------------------+ | BIT_LENGTH('MySQL') | +---------------------+ | 40 | +---------------------+
CHAR(number1[, number2[, ...]])
This function returns the characters that would result if each number were an integer converted from ASCII code, skipping null values. Decimals are rounded to the nearest integer value.
For example:
mysql> SELECT CHAR(97,101,105,111,117); +--------------------------+ | CHAR(97,101,105,111,117) | +--------------------------+ | aeiou | +--------------------------+ mysql> SELECT CHAR(97.6,101,105,111,117); +----------------------------+ | CHAR(0.97,101,105,111,117) | +----------------------------+ | beiou | +----------------------------+
Synonym for the LENGTH() function, except that multibyte characters are only counted once.
Synonym for the LENGTH() function, except that multibyte characters are only counted once.
CONCAT(string1[,string2[,...]])
Concatenates the string arguments and returns the resulting string or NULL if any argument is null. Arguments that are not strings are converted to strings.
For example:
mysql> SELECT CONCAT('a','b'); +-----------------+ | CONCAT('a','b') | +-----------------+ | ab | +-----------------+ mysql> SELECT CONCAT('a',12); +----------------+ | CONCAT('a',12) | +----------------+ | a12 | +----------------+ mysql> SELECT CONCAT(.3,'NULL'); +-------------------+ | CONCAT(.3,'NULL') | +-------------------+ | 0.3NULL | +-------------------+ mysql> SELECT CONCAT(.3,NULL); +-----------------+ | CONCAT(.3,NULL) | +-----------------+ | NULL | +-----------------+
CONCAT_WS(separator, string1[, string2[, ...]])
Similar to CONCAT, except that the first argument is a separator placed between each of the concatenated strings. Will skip any null strings (except the separator, in which case the result will be NULL). The separator does not have to be a string.
For example:
mysql> SELECT CONCAT_WS('-','a','b'); +------------------------+ | CONCAT_WS('-','a','b') | +------------------------+ | a-b | +------------------------+ mysql> SELECT CONCAT_WS(1,.3,.4); +--------------------+ | CONCAT_WS(1,.3,.4) | +--------------------+ | 0.310.4 | +--------------------+ mysql> SELECT CONCAT_WS(NULL,'a','b'); +-------------------------+ | CONCAT_WS(NULL,'a','b') | +-------------------------+ | NULL | +-------------------------+ mysql> SELECT CONCAT_WS('-','a',NULL,'c'); +-----------------------------+ | CONCAT_WS('-','a',NULL,'c') | +-----------------------------+ | a-c | +-----------------------------+
CONV(number,from_base,to_base)
Converts a number from one base to another. Returns the converted number represented as string, 0 if the conversion cannot be made (the function will convert as far as it can from the left), and NULL if the number is null. The number is assumed to be an integer, but it can be passed as a string. It is assumed to be unsigned unless the to base is a negative number. The bases can be anything between 2 and 36 (with to_base possibly being negative).
For example:
mysql> SELECT CONV(10,2,10); +---------------+ | CONV(10,2,10) | +---------------+ | 2 | +---------------+ mysql> SELECT CONV('a',16,2); +----------------+ | CONV('a',16,2) | +----------------+ | 1010 | +----------------+ mysql> SELECT CONV('3f',16,10); +------------------+ | CONV('3f',16,10) | +------------------+ | 63 | +------------------+ mysql> SELECT CONV('z3',16,10); +------------------+ | CONV('z3',16,10) | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT CONV('3z',16,10); +------------------+ | CONV('3z',16,10) | +------------------+ | 3 | +------------------+
ELT(number, string1 [,string2, ...])
Uses number as an index to decide which string to return; 1 returns the first string, 2 the second, and so on. Returns NULL if there is no matching string.
For example:
mysql> SELECT ELT(2,'one','two'); +--------------------+ | ELT(2,'one','two') | +--------------------+ | two | +--------------------+ mysql> SELECT ELT(0,'one','two'); +--------------------+ | ELT(0,'one','two') | +--------------------+ | NULL | +--------------------+
The FIELD() function is the complement of ELT().
EXPORT_SET(number,on,off[,separator[,number_of_bits]])
Examines the number in binary, and for each bit that is set, returns on, and for each that doesn't, returns off. The default separator is a comma, but you can specify something else. Sixty-four bits is used, but you can change the number_of_bits.
For example:
mysql> SELECT EXPORT_SET(2,1,0,' ',4); +-------------------------+ | EXPORT_SET(2,1,0,' ',4) | +-------------------------+ | 0 1 0 0 | +-------------------------+ mysql> SELECT EXPORT_SET(7,'ok','never',' : ',6); +--------------------------------------+ | EXPORT_SET(7,'ok','never',' : ',6) | +--------------------------------------+ | ok : ok : ok : never : never : never | +--------------------------------------+
FIELD(string, string1 [, string2 , ...])
Returns the index of string in the list following. If string1 matches, the index will be 1. If it's string2 then it will be 2, and so on. It will return 0 if the string is not found.
For example:
mysql> SELECT FIELD('b','a','b','c'); +------------------------+ | FIELD('b','a','b','c') | +------------------------+ | 2 | +------------------------+ mysql> SELECT FIELD('a','aa','b','c'); +-------------------------+ | FIELD('a','aa','b','c') | +-------------------------+ | 0 | +-------------------------+
FIND_IN_SET(string,stringlist)
Similar to FIELD() in that it returns an index matching the string, but this function searches either a string separated by commas or the type SET. It will return 1 if the string matches the first substring before the comma (or the element of the set), 2 if the second substring matches, and so on. It returns 0 if there is no match. Note that it matches whole comma-separated substrings, not just any portions of the string.
For example:
mysql> SELECT FIND_IN_SET('b','a,b,c'); +--------------------------+ | FIND_IN_SET('b','a,b,c') | +--------------------------+ | 2 | +--------------------------+ mysql> SELECT FIND_IN_SET('a','aa,bb,cc'); +-----------------------------+ | FIND_IN_SET('a','aa,bb,cc') | +-----------------------------+ | 0 | +-----------------------------+ 1 row in set (0.00 sec)
HEX(string or number)
Returns the hexadecimal value (a string representation) of the specified BIGINT number, 0 if the number cannot be converted (the function will convert as far as it can from the left), or NULL if it's null.
If the argument is a number, this is converted to hexadecimal (equivalent to the CONV(number,10,16) function). If it's a string, each character in the string is converted to its numerical equivalent in the ASCII table (for example, a = 97, b = 98, and so on), and each of those numbers is in turn converted to its hexadecimal equivalent.
For example:
mysql> SELECT HEX(13); +---------+ | HEX(13) | +---------+ | D | +---------+ mysql> SELECT ORD('a'); +----------+ | ORD('a') | +----------+ | 97 | +----------+ 1 row in set (0.00 sec) mysql> SELECT ORD('b'); +----------+ | ORD('b') | +----------+ | 98 | +----------+ 1 row in set (0.00 sec) mysql> SELECT HEX(97); +---------+ | HEX(97) | +---------+ | 61 | +---------+ 1 row in set (0.00 sec) mysql> SELECT HEX(98); +---------+ | HEX(98) | +---------+ | 62 | +---------+ 1 row in set (0.00 sec) mysql> SELECT HEX('ab'); +-----------+ | HEX('ab') | +-----------+ | 6162 | +-----------+ 1 row in set (0.00 sec)
INSERT(string,position,length,newstring)
Replaces the portion of the string starting at position and continuing for length characters with newstring. The length of newstring and the length specified can differ, in which case the original string will change in length.
The function is multibyte safe.
For example:
mysql> SELECT INSERT('MySQL',1,0,'What is '); +--------------------------------+ | INSERT('MySQL',1,0,'What is ') | +--------------------------------+ | What is MySQL | +--------------------------------+ mysql> SELECT INSERT('MySQL',1,1,'P'); +-------------------------+ | INSERT('MySQL',1,1,'P') | +-------------------------+ | PySQL | +-------------------------+ mysql> SELECT INSERT('MySQL',1,1,'Py'); +--------------------------+ | INSERT('MySQL',1,1,'Py') | +--------------------------+ | PyySQL | +--------------------------+
INSTR(string,substring)
Searches the string case insensitively (unless either string is binary) for the first occurrence of substring and returns the position or returns 0 if substring was not found. The first letter is at position 1.
For example:
mysql> SELECT INSTR('MySQL','My'); +---------------------+ | INSTR('MySQL','My') | +---------------------+ | 1 | +---------------------+ mysql> SELECT INSTR('Cecilia','i'); +----------------------+ | INSTR('Cecilia','i') | +----------------------+ | 4 | +----------------------+
LCASE(string)
Synonym for LOWER().
LEFT(string,length)
Returns the leftmost length characters from the string. This function is multibyte safe.
For example:
mysql> SELECT LEFT('abc',2); +---------------+ | LEFT('abc',2) | +---------------+ | ab | +---------------+
LENGTH(string)
Returns the length in characters of the string. Converts the argument to a string if it can.
For example:
mysql> SELECT LENGTH('MySQL'); +-----------------+ | LENGTH('MySQL') | +-----------------+ | 5 | +-----------------+ mysql> SELECT LENGTH(99); +------------+ | LENGTH(99) | +------------+ | 2 | +------------+
CHAR_LENGTH(), CHARACTER_LENGTH(), and OCTET_LENGTH() are synonyms, except that multibyte characters are only counted once with CHAR_LENGTH() and CHARACTER_LENGTH().
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 because of one of the previous reasons, the function returns NULL.
For example, if the file /home/iang/test.txt contained the text 123456, you'd return the following with LOAD_FILE():
mysql> SELECT LOAD_FILE('/home/iang/test.txt'); +----------------------------------+ | LOAD_FILE('/home/iang/test.txt') | +----------------------------------+ | 123456 | +----------------------------------+
LOAD_FILE is often used with uploading BLOBs into the database.
For example:
mysql> INSERT INTO table_with_blob(id,image) VALUES(1,LOAD_FILE('/images/pic.jpg'));
LOCATE(substring, string [,position])
Searches the string case insensitively (unless either string is binary) for the first occurrence of substring and returns the position or returns 0 if substring was not found. If the optional position argument it supplied, the search starts at that point. The first letter is at position 1.
For example:
mysql> SELECT LOCATE('My','MySQL'); +----------------------+ | LOCATE('My','MySQL') | +----------------------+ | 1 | +----------------------+ mysql> SELECT LOCATE('C','Cecilia',2); +-------------------------+ | LOCATE('C','Cecilia',2) | +-------------------------+ | 3 | +-------------------------+
This is the same as the INSTR() function but with the arguments reversed.
LOWER(string)
Returns a string with all characters converted to lowercase (according to the current character set mapping). The function is multibyte safe.
For example:
mysql> SELECT LOWER('AbC'); +--------------+ | LOWER('AbC') | +--------------+ | abc | +--------------+
The LCASE() function is a synonym.
LPAD(string,length,padding_string)
Left-pads the string with the padding_string until the result is length characters long. If the string is longer than the length, it will be shortened to length characters.
For example:
mysql> SELECT LPAD('short',7,'-'); +---------------------+ | LPAD('short',7,'-') | +---------------------+ | --short | +---------------------+ mysql> SELECT LPAD('too_long',7,' '); +------------------------+ | LPAD('too_long',7,' ') | +------------------------+ | too_lon | +------------------------+ mysql> SELECT LPAD('a',4,'12'); +------------------+ | LPAD('a',4,'12') | +------------------+ | 121a | +------------------+
LTRIM(string)
Removes leading spaces from the string and returns the result.
For example:
mysql> SELECT LTRIM(' Yes'); +-----------------+ | LTRIM(' Yes') | +-----------------+ | Yes | +-----------------+
MAKE_SET(number, string1 [, string2, ...])
Returns a set (string where the elements are comma separated) with the strings that match the number converted to binary. The first string appears if bit 0 is set, the second string if bit1 is set, and so on. If the bit argument is set to 3, then the first two strings are returned because 3 is 11 in binary.
For example:
mysql> SELECT MAKE_SET(3,'a','b','c'); +-------------------------+ | MAKE_SET(3,'a','b','c') | +-------------------------+ | a,b | +-------------------------+ mysql> SELECT MAKE_SET(5,'a','b','c'); +-------------------------+ | MAKE_SET(5,'a','b','c') | +-------------------------+ | a,c | +-------------------------+
OCT(number)
Returns the octal value (a string representation) of the specified BIGINT number, 0 if the number cannot be converted (the function will convert as far as it can from the left), or NULL if it's null.
For example:
mysql> SELECT OCT(09); +---------+ | OCT(09) | +---------+ | 11 | +---------+ mysql> SELECT OCT('a1'); +-----------+ | OCT('a1') | +-----------+ | 0 | +-----------+ mysql> SELECT OCT('13b'); +------------+ | OCT('13b') | +------------+ | 15 | +------------+
This function is equivalent to CONV(number,10,8).
Synonym for the LENGTH() function.
ORD(string)
Returns the ASCII value of the first (leftmost) character of the string, 0 if the string is empty, and NULL if the string is null. This is the same as the ASCII function, unless the character is a multibyte character, in which case the value is calculated as a base 256 number— that is, each byte being worth 256 times more than the next byte. For example, the formula for a two-byte character would be as follows: (byte_1_ASCII code * 256) + (byte_2_ASCII_ code).
For example:
mysql> SELECT ORD("a"); +----------+ | ORD("a") | +----------+ | 97 | +----------+ mysql> SELECT ORD("az"); +-----------+ | ORD("az") | +-----------+ | 97 | +-----------+
You can use the BIN(), OCT(), and HEX() functions to convert decimal numbers to binary, octal, and hexadecimal, respectively.
POSITION(substring IN string)
Searches case insensitively (unless either argument is a binary string) for the first occurrence of substring in string and returns the position (starting at 1) or returns 0 if substring was not found. The function is multibyte safe.
For example:
mysql> SELECT POSITION('i' IN 'Cecilia'); +----------------------------+ | POSITION('i' IN 'Cecilia') | +----------------------------+ | 4 | +----------------------------+
QUOTE(string)
Escapes the single quote ('), double quote (") ASCII NULL, and Ctrl+Z characters, and surrounds the string with single quotes so it can be safely used in an SQL statement. Single quotes are not added if the argument is NULL.
For example:
mysql> SELECT QUOTE("What's Up?"); +---------------------+ | QUOTE("What's Up?") | +---------------------+ | 'What\'s Up?' | +---------------------+
REPEAT(string,count)
Repeats the string argument count times and returns the result, returns an empty string if count is not positive, or returns NULL if either argument in null.
For example:
mysql> SELECT REPEAT('a',4); +---------------+ | REPEAT('a',4) | +---------------+ | aaaa | +---------------+ mysql> SELECT REPEAT('a',-1); +----------------+ | REPEAT('a',-1) | +----------------+ | | +----------------+ mysql> SELECT REPEAT('a',NULL); +------------------+ | REPEAT('a',NULL) | +------------------+ | NULL | +------------------+
REPLACE(string,from_string,to_string)
Replaces all occurrences of from_str found in the string with to_str and returns the result. The function is multibyte safe.
For example:
mysql> SELECT REPLACE('ftp://test.host.co.za','ftp','http'); +-----------------------------------------------+ | REPLACE('ftp://test.host.co.za','ftp','http') | +-----------------------------------------------+ | http://test.host.co.za | +-----------------------------------------------+
REVERSE(string)
Reverses the order of the characters in string and returns the result. This function is multibyte safe.
For example:
mysql> SELECT REVERSE('abc'); +----------------+ | REVERSE('abc') | +----------------+ | cba | +----------------+
RIGHT(string,length)
Returns the rightmost length characters from the string. This function is multibyte safe.
For example:
mysql> SELECT RIGHT('abc',2); +----------------+ | RIGHT('abc',2) | +----------------+ | bc | +----------------+
RPAD(string,length,padding_string)
Right-pads the string with the padding_string until the result is length characters long. If the string is longer than the length, it will be shortened to length characters.
For example:
mysql> SELECT RPAD('short',7,'-'); +---------------------+ | RPAD('short',7,'-') | +---------------------+ | short-- | +---------------------+ mysql> SELECT RPAD('too_long',7,' '); +------------------------+ | RPAD('too_long',7,' ') | +------------------------+ | too_lon | +------------------------+ mysql> SELECT RPAD('a',4,'12'); +------------------+ | RPAD('a',4,'12') | +------------------+ | a121 | +------------------+
RTRIM(string)
Removes trailing spaces from the string and returns the result.
For example:
mysql> SELECT CONCAT('a',RTRIM('b '),'c'); +------------------------------------+ | CONCAT('a',RTRIM('b '),'c') | +------------------------------------+ | abc | +------------------------------------+
SOUNDEX(string)
Returns a soundex string, which is a phonetic string designed as a more convenient way of indexing to overcome misspellings. Strings that sound the same will have the same soundex strings. This is usually four characters long, but this function returns a string of arbitrary length. Use the SUBSTRING() function on top of SOUNDEX() to return a standard soundex string. Nonalphanumeric characters are ignored, and non-English international alphabetical characters are treated as vowels.
For example:
mysql> SELECT SOUNDEX('MySQL'); +------------------+ | SOUNDEX('MySQL') | +------------------+ | M240 | +------------------+ mysql> SELECT SOUNDEX('MySequl'); +--------------------+ | SOUNDEX('MySequl') | +--------------------+ | M240 | +--------------------+
SPACE(number)
Returns a string consisting of number spaces.
For example:
mysql> SELECT "A",SPACE(10),"B"; +---+------------+---+ | A | SPACE(10) | B | +---+------------+---+ | A | | B | +---+------------+---+
SUBSTRING(string, position [,length]) SUBSTRING(string FROM position [FOR length])
Returns a substring of the string argument starting at the position (which starts at 1) and optionally with the specified length.
For example:
mysql> SELECT SUBSTRING('MySQL',2); +----------------------+ | SUBSTRING('MySQL',2) | +----------------------+ | ySQL | +----------------------+ mysql> SELECT SUBSTRING('MySQL' FROM 3); +---------------------------+ | SUBSTRING('MySQL' FROM 3) | +---------------------------+ | SQL | +---------------------------+ 1 row in set (0.16 sec) mysql> SELECT SUBSTRING('MySQL',1,2); +------------------------+ | SUBSTRING('MySQL',1,2) | +------------------------+ | My | +------------------------+ 1 row in set (0.22 sec)
The function is multibyte safe. The function MID(string,position,length) is a synonym for SUBSTRING(string,position,length).
SUBSTRING_INDEX(string,delimiter,count)
Returns the substring from the string up until count (if count is positive) or beyond count (if count is negative) occurrences of delimiter.
The function is multibyte safe.
For example:
mysql> SELECT SUBSTRING_INDEX('a||b||c||d','||',3); +--------------------------------------+ | SUBSTRING_INDEX('a||b||c||d','||',3) | +--------------------------------------+ | a||b||c | +--------------------------------------+ mysql> SELECT SUBSTRING_INDEX('I am what I am','a',2); +-----------------------------------------+ | SUBSTRING_INDEX('I am what I am','a',2) | +-----------------------------------------+ | I am wh | +-----------------------------------------+ mysql> SELECT SUBSTRING_INDEX('I am what I am','a',-2); +------------------------------------------+ | SUBSTRING_INDEX('I am what I am','a',-2) | +------------------------------------------+ | t I am | +------------------------------------------+
TRIM([[BOTH | LEADING | TRAILING] [trim_string] FROM] string)
If none of the optional parameters are specified, TRIM() removes leading and trailing spaces. You specify LEADING or TRAILING to only remove the one kind or specify the default BOTH. You can also remove something besides spaces by specifying trim_string. The function is multibyte safe.
For example:
mysql> SELECT TRIM(' What a waste of space ') AS t; +-----------------------+ | t | +-----------------------+ | What a waste of space | +-----------------------+ mysql> SELECT TRIM(LEADING '0' FROM '0001'); +-------------------------------+ | TRIM(LEADING '0' FROM '0001') | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT TRIM(LEADING FROM ' 1'); +---------------------------------+ | TRIM(LEADING FROM ' 1') | +---------------------------------+ | 1 | +---------------------------------+ mysql> SELECT TRIM(BOTH 'abc' FROM 'abcabcaabbccabcabc'); +--------------------------------------------+ | TRIM(BOTH 'abc' FROM 'abcabcaabbccabcabc') | +--------------------------------------------+ | aabbcc | +--------------------------------------------+
UCASE(string)
Synonym for UPPER().
UPPER(string)
Returns a string with all characters converted to uppercase (according to the current character set mapping). The function is multibyte safe.
For example:
mysql> SELECT UPPER('aBc'); +--------------+ | UPPER('aBc') | +--------------+ | ABC | +--------------+
The UCASE() function is a synonym.
|
|