Mastering MySQL 4 [Electronic resources] نسخه متنی

String Functions

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

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

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

BIT_LENGTH(string)

Returns the string length in bits.

For example:

mysql> SELECT BIT_LENGTH('MySQL');
+---------------------+
| BIT_LENGTH('MySQL') |
+---------------------+
| 40                  |
+---------------------+

CHAR

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                      |
+----------------------------+

CHAR_LENGTH

Synonym for the LENGTH() function, except that multibyte characters are only counted once.

CHARACTER_LENGTH

Synonym for the LENGTH() function, except that multibyte characters are only counted once.

CONCAT

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

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

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

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

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

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

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

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

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

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

LCASE(string)

Synonym for LOWER().

LEFT

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

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

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

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

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

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

LTRIM(string)

Removes leading spaces from the string and returns the result.

For example:

mysql> SELECT LTRIM('   Yes');
+-----------------+
| LTRIM('   Yes') |
+-----------------+
| Yes             |
+-----------------+

MAKE_SET

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

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).

OCTET_LENGTH

Synonym for the LENGTH() function.

ORD

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

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

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

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

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

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

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

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

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

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

SPACE(number)

Returns a string consisting of number spaces.

For example:

mysql> SELECT "A",SPACE(10),"B";
+---+------------+---+
| A | SPACE(10)  | B |
+---+------------+---+
| A |            | B |
+---+------------+---+

SUBSTRING

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

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

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

UCASE(string)

Synonym for UPPER().

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.