String Functions

String functions perform operations on string data types such as VARCHAR, CHAR, and TEXT.

9.1.1. ASCII

 string1 =ASCII (string2 )

ASCII returns the ASCII character code corresponding to the first character in the provided input string.

Since the ASCII function returns only the ASCII code for the first character, we can create a stored function to extend this capability to allow us to return the ASCII codes corresponding to all of the characters in the string. Example 9-1 shows an implementation of such a stored function. It uses the LENGTH and SUBSTR functions to extract each character in the input string, and then uses the ASCII and CONCAT functions to build up a string consisting of all of the ASCII codes corresponding to the entire input string.

Example 9-1. Using the ASCII function

CREATE FUNCTION ascii_string (in_string VARCHAR(80) )
 RETURNS VARCHAR(256)
 DETERMINISTIC
BEGIN
 DECLARE i INT DEFAULT 1;
 DECLARE string_len INT;
 DECLARE out_string VARCHAR(256) DEFAULT '';

 SET string_len=LENGTH(in_string);
 WHILE (iASCII(SUBSTR(in_string,i,1)),' ');
 SET i=i+1;
 END WHILE;
 RETURN (out_string);

END
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SELECT ascii_string('MySQL Rocks!')
--------------

+---------------------------------------+
| ascii_string('MySQL Rocks!') |
+---------------------------------------+
| 77 121 83 81 76 32 82 111 99 107 115 |
+---------------------------------------+
1 row in set (0.00 sec)

9.1.2. CHAR

 string =CHAR (ascii code  [,... ])

CHAR returns the characters corresponding to one or more ASCII codes provided. Example 9-2 uses the CHAR function to create a temporary table containing the ASCII characters for the first 128 ASCII codes.

Example 9-2. Using the CHAR function to generate an ASCII chart

CREATE PROCEDURE ascii_chart( )
BEGIN
 DECLARE i INT DEFAULT 1;

 CREATE TEMPORARY TABLE ascii_chart
 (ascii_code INT, ascii_char CHAR(1));

 WHILE (i<=128) DO
 INSERT INTO ascii_chart VALUES(i,CHAR(i));
 SET i=i+1;
 END WHILE;

END
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
CALL ascii_chart( )
--------------

Query OK, 1 row affected (5.96 sec)

--------------
SELECT * FROM ascii_chart
--------------

+------------+------------+
| ascii_code | ascii_char |
+------------+------------+
| 1 | 

| | 2 |

| | 3 | | | 4 | | | 5 | | | 6 | | | 7 | | | 8 | | | 9 | | | 10 | | | 11 | | | 12 | | | 13 | | 14 |

| | 15 |

| | 16 | | | 17 | | | 18 | | | 19 | !! | | 20 | ¶ | | 21 | § | | 22 | -- | | 23 | _ | | 24 | | | 25 | | | 26 | | | 27 | |

9.1.3. CHARSET

 character_set =CHARSET (string )

CHARSET returns the character set of the supplied string.

 SET var1=CHARSET("My name is Guy") ;  latin1

 

9.1.4. CONCAT

 string1 =CONCAT (string2  [,... ])

CONCAT returns a string consisting of the concatenation of all of the supplied input strings. If any of the input strings is NULL, then CONCAT will also return NULL.

Example 9-3 uses the CONCAT function to create a well-formatted name includingif appropriatetitle and middle initial. First, we use the ISNULL function to check for NULLs in the input string so as to avoid inadvertently returning a NULL string if one of the inputs is NULL.

Example 9-3. Using CONCAT to concatenate strings

CREATE FUNCTION concat_example(in_title VARCHAR(4),
 in_gender CHAR(1),
 in_firstname VARCHAR(20),
 in_middle_initial CHAR(1),
 in_surname VARCHAR(20))

 RETURNS VARCHAR(60)
BEGIN
 DECLARE l_title VARCHAR(4);
 DECLARE l_name_string VARCHAR(60);

 IF ISNULL(in_title) THEN
 IF in_gender='M' THEN
 SET l_title='Mr';
 ELSE
 SET l_title='Ms';
 END IF;
 END IF;

 IF ISNULL(in_middle_initial) THEN
 SET l_name_string=CONCAT(l_title,' ',in_firstname,' ',in_surname);
 ELSE
 SET l_name_string=CONCAT(l_title,' ',in_firstname,' ',
 in_middle_initial,' ',in_surname);
 END IF;

 RETURN(l_name_string);
END;
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SELECT concat_example(null,'F','Mary',null,'Smith')
--------------

+----------------------------------------------+
| concat_example(null,'F','Mary',null,'Smith') |
+----------------------------------------------+
| Ms Mary Smith |
+----------------------------------------------+
1 row in set (0.00 sec)

If your database is running in ANSI mode (sql_mode='ANSI') or if the sql_mode variable includes the PIPES_AS_CONCAT setting, you can use the || (pipe) characters to concatenate strings. The use of pipe characters to indicate concatenation in stored programs is dependent on the setting of sql_mode when the stored program is created, not when it runs. So you can happily use the || method of concatenating strings provided that you set sql_mode='ANSI'when you create the program. If the program runs when sql_mode is set to some other value, the stored program will still return the correct results.

Example 9-4 illustrates the use of ANSI mode and || characters to perform string concatenation. Note that while sql_mode was set to 'ANSI' when the stored function was created, the stored program still returned the correct results even though the sql_mode had been set to 'trADITIONAL' at runtime.

Example 9-4. Using || to concatenate when sql_mode=ANSI

set sql_mode='ANSI'
--------------

Query OK, 0 rows affected (0.00 sec)
--------------
CREATE FUNCTION concat_example_ansi(
 in_title VARCHAR(4),
 in_gender CHAR(1),
 in_firstname VARCHAR(20),
 in_middle_initial CHAR(1),
 in_surname VARCHAR(20))

 RETURNS VARCHAR(60)
BEGIN
 DECLARE l_title VARCHAR(4);
 DECLARE l_name_string VARCHAR(60);

 IF ISNULL(in_title) THEN
 IF in_gender='M' THEN
 SET l_title='Mr';
 ELSE
 SET l_title='Ms';
 END IF;
 END IF;

 IF ISNULL(in_middle_initial) THEN
 SET l_name_string=l_title||' '||in_firstname||' '||in_surname;
 ELSE
 SET l_name_string=l_title||' '||in_firstname||' '||
 in_middle_initial||' '||in_surname;
 END IF;

 RETURN(l_name_string);
END;
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SET sql_mode='TRADITIONAL'
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SELECT concat_example_ansi(null,'F','Mary',null,'Smith')
--------------

+---------------------------------------------------+
| concat_example_ansi(null,'F','Mary',null,'Smith') |
+---------------------------------------------------+
| Ms Mary Smith |
+---------------------------------------------------+

9.1.5. CONCAT_WS

 string1 =CONCAT_WS (delimiter ,string2  [,... ])

CONCAT_WS acts like the CONCAT function, but it inserts the specified delimiter between each string. Note in Example 9-3 that we manually inserted single space characters between each string, as shown below:

 SET l_name_string=CONCAT(l_title,' ',in_firstname,' ',
 in_middle_initial,' ',in_surname);

Using CONCAT_WS, we could simplify this statement as follows:

 SET l_name_string=CONCAT_WS(' ',l_title ,in_firstname ,
 in_middle_initial,in_surname);

 

9.1.6. INSERT

 string =INSERT (original_string ,position ,length ,new_string )

INSERT inserts new_string into the original_string at the specified position, optionally overwriting up to length characters of the original string.

Example 9-5 shows how we might use the INSERT function to emulate the MySQL REPLACE function to implement "search and replace" functionality. We first use the INSTR function to find the location of the "find string" and then replace it with the "replace string." We set length to the length of the find string so that the find string is overwritten with the replace string, even if the two strings are of different lengths.

Example 9-5. Using the INSERT function

CREATE FUNCTION my_replace
 (in_string VARCHAR(255),
 in_find_str VARCHAR(20),
 in_repl_str VARCHAR(20))

 RETURNS VARCHAR(255)
BEGIN
 DECLARE l_new_string VARCHAR(255);
 DECLARE l_find_pos INT;

 SET l_find_pos=INSTR(in_string,in_find_str);

 IF (l_find_pos>0) THEN
 SET l_new_string=INSERT(in_string,l_find_pos,LENGTH(in_find_str),in_repl_str);
 ELSE
 SET l_new_string=in_string;
 END IF;
 RETURN(l_new_string);

END
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SELECT my_replace('We love the Oracle server','Oracle','MySQL')
--------------

+----------------------------------------------------------+
| my_replace('We love the Oracle server','Oracle','MySQL') |
+----------------------------------------------------------+
| We love the MySQL server |
+----------------------------------------------------------+
1 row in set (0.00 sec)

9.1.7. INSTR

 position =INSTR (string ,substring )

INSTR returns the location of the first occurrence of a substring within a string. If no occurrence of the substring is found, INSTR returns 0.

In Example 9-5 we used INSTR to locate the "find string" within a string prior to using INSERT to replace that string with the "replace string."

9.1.8. LCASE

 string1 =LCASE (string2 )

LCASE returns an input string with any of its uppercase letters translated to lowercase. Nonalphabetic characters are ignored.

Here are some examples of the effect of LCASE:

 SET a=LCASE('McTavish Jewelers');  'mctavish jewelers
 SET b=LCASE('23rd June');  '23rd june

 

9.1.9. LEFT

 string =LEFT (string2 ,length )

LEFT returns the leftmost characters (the number is specified by length) in the input string.

 SET a=LEFT('Hi There',2);  'Hi

 

9.1.10. LENGTH

 characters =LENGTH (string )

LENGTH returns the number of bytes in the input string. For single-byte character sets (e.g., English, Swedish), this is equivalent to the number of characters in the string. However, for multibyte character sets (e.g., Kanji, Klingon), you may be better off using the CHAR_LENGTH function, which returns the number of characters rather than the number of bytes.

 SET a=LENGTH(null);  NULL
 SET b=LENGTH('');  0
 SET c=LENGTH('Guy');  3
 SET d=LENGTH('Guy ');  4

 

9.1.11. LOAD_FILE

 string =LOAD_FILE (file_name )

LOAD_FILE loads the contents of the specified file into a variable of a suitable data typeusually BLOB or TEXT. The file has to be accessible to the MySQL serverthat is, the file needs to exist on the machine that hosts the MySQL server, and the server needs to have sufficient permissions to read the file.

Example 9-6 shows how we can use the LOAD_FILE function to load the contents of an operating system file and report the number of bytes loaded. Note that on Windows we need to use double-backslash characters, \, instead of single slashes as directory separators. Thus, in order to specify the file 'c: mpmydata.txt' we specified 'c:\tmp\mydata.txt'.

Example 9-6. Using LOAD_FILE to read an OS file

CREATE PROCEDURE filesize(in_file_name VARCHAR(128))

BEGIN
 DECLARE mytext TEXT;
 SET mytext=LOAD_FILE(in_file_name);
 SELECT in_file_name||' contains '||length(mytext)||' bytes'
 AS output;
END
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
CALL filesize('c:\tmp\mydata.txt')
--------------

+-------------------------------------+
| output |
+-------------------------------------+
| c:	mpmydata.txt contains 98 bytes |
+-------------------------------------+
1 row in set (0.02 sec)

9.1.12. LOCATE

 position =LOCATE (substring , string  [,start_position ] )

LOCATE is similar to the INSTR function in that it searches for the location of a substring within a string. However, it also allows us to specify a starting position for the search. If the substring is not found, LOCATE returns 0.

In Example 9-7 we use LOCATE to count the number of occurrences of a substring within a string. Once we find an instance of the substring, we set the starting position to just past that string and repeat until all instances of the substring have been found.

Example 9-7. Using LOCATE to find substrings

CREATE FUNCTION count_strings
 (in_string VARCHAR(256),in_substr VARCHAR(128))
 RETURNS INT
 DETERMINISTIC
BEGIN
 DECLARE l_count INT DEFAULT 0;
 DECLARE l_start INT DEFAULT 1;
 DECLARE l_pos INT;

 MainLoop:
 LOOP
 SET l_pos=LOCATE(in_substr,in_string,l_start);
 IF l_pos=0 THEN
 LEAVE MainLoop;
 ELSE
 SET l_count=l_count+1;
 SET l_start=l_pos+1;
 END IF;

 END LOOP;
 RETURN(l_count);
END
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
SELECT count_strings('She sells sea shells by the sea shore','sea') as count
--------------

+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)

9.1.13. LPAD

 string1 =LPAD (string2 ,length ,pad )

LPAD adds occurrences of the pad string to the input string until the output string reaches the specified length.

 SET a=LPAD('Hello',10,'.');  '.....Hello
 SET b=lpad('hi',10,'( )');  '()()()( )hi

 

9.1.14. LTRIM

 string1 =LTRIM (string2 )

LTRIM TRims any leading spaces from a string.

 SET a=LTRIM(' Hello');  'Hello

 

9.1.15. REPEAT

 string1 =REPEAT (string2 ,count )

REPEAT returns a string in which the input string is repeated count times.

 SET a=REPEAT('Dive! ',3);  'Dive! Dive! Dive!'

 

9.1.16. REPLACE

 string1 =REPLACE (string2 ,search_string ,replace_string )

REPLACE returns a string in which all occurrences of the search_string are replaced by the replace_string.

 SET a=REPLACE('Monty & David','&','and');  'Monty and David

 

9.1.17. RPAD

 string1 =RPAD (string2 ,length ,pad )

RPAD adds a sequence of pad characters to the string up to the specified length.

 SET var1=RPAD("MySQL",10,".") ;  MySQL.....

 

9.1.18. RTRIM

 string1 =RTRIM (string2 )

RTRIM trims any trailing spaces from a string.

 SET a=RTRIM('Guy ');  'Guy

 

9.1.19. STRCMP

 position =STRCMP (string1 ,string2 )

STRCMP compares two strings and determines if the first string is "before" or "after" the second string in the ASCII collation sequence. The function returns -1 if the first string is before the second string, 1 if the first string collates after the second string, and 0 if the two strings are identical.

 SET a=STRCMP('Guy','Guy')  0
 SET b=STRCMP('Guy','Steven')  -1
 SET c=STRCMP('Steven','Guy')  1

 

9.1.20. SUBSTRING

 string1 =SUBSTRING (string2 , position  [,length ])

SUBSTRING returns a portion of the supplied string starting at the specified position from the beginning of the string (starting at 1). If a negative position is specified, then the substring commences from the end of the string; for example, -2 indicates the second to last character of the string. If length is omitted, SUBSTRING returns all of the remaining portion of the input string.

 SET a=SUBSTR('MySQL AB',7)  'AB
 SET b=SUBSTR('MySQL AB',-2)  'AB
 SET c=SUBSTR('MySQL AB',3,3)  'SQL

 

9.1.21. TRIM

 string1=trIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2)

trIM strips leading and/or trailing characters from a string. By default, it trims both leading and trailing spaces.

 SET a=TRIM(LEADING '>' FROM '>>>>>>>>>Fred');  'Fred
 SET b=TRIM(BOTH '-' FROM '---------Fred-------');  'Fred
 SET c=TRIM(BOTH FROM ' Guy ')  'Guy;
 SET d=TRIM(' Guy ');  'Guy

 

9.1.22. UCASE

 string1 =UCASE (string2 )

UCASE converts a string to uppercase.

9.1.23. Other String Functions

Table 9-1 lists the string functions not covered in previous sections. Some of these functions are aliases for functions we have already discussed, while others are rarely used in mainstream MySQL programming. You can find out more about these functions by reading the section "Functions and Operators" in the MySQL Reference Manual, available online.

Table 9-1. Additional string functions

Function	Syntax	Description
BINARY	string1=BINARY(string2)	Returns the binary representation of a string. This function can be used to force case-sensitive comparisons when they would otherwise not occur.
BIT_LENGTH	bits=BIT_LENGTH(string)	Returns the number of bits in a string.
CHAR_LENGTH	length=CHAR_LENGTH(string)	Returns the number of characters in a string. Like LENGTH, except that it returns the number of characters, rather than the number of bytes, for multibyte character sets.
CHARACTER_LENGTH	length=CHARACTER_LENGTH(string)	Alias for CHAR_LENGTH.
COMPRESS	string1=COMPRESS(string2)	Returns a compressed version of a string.
DECODE	string1=DECODE(string2,password)	Decrypts a string that has been encrypted with ENCRYPT .
ELT	string1=ELT(number,string2[,...])	Returns one of the elements in a list.
ENCODE	string1=ENCODE(string2,password)	Encrypts a string. The string can be decrypted with DECODE.
ENCRYPT	string1=ENCRYPT(string2,seed)	Encrypts a string. The string cannot be decrypted with DECODE.
EXPORT_SET	string=ENCODE_SET(number,on_string, off_string,seperator,no_of_bits)	Returns the binary representation of a number encoded with strings for on and off bits.
FIELD	number=FIELD(string1,string2[,...])	Searches for a string in a list of strings.
INET_ATON	number=INET_ATON(IPAddress)	Converts an IP address into a numeric representation.
INET_NTOA	IPAddress=INET_NTOA(number)	Converts a number into a corresponding IP address.
LOWER	string1=LOWER(string2)	Synonym for LCASE.
MID	string1=MID(string2,start [,length])	Returns a substring. Similar to SUBSTR.
OCTET_LENGTH	length=OCTET_LENGTH(string)	Alias for LENGTH.
ORD	position=ORD(string)	Returns the ordinal value of the character in the ASCII character set.
PASSWORD	string1=PASSWORD(string2)	Encrypts the given string as a MySQL password.
POSITION	position=POSITION(substring IN string)	Returns the position of the substring in the string. Similar to LOCATE.
QUOTE	string1=QUOTE(string2)	Returns a string with special characters preceded by an escape character.
REVERSE	string1=REVERSE(string2)	Reverses the order of characters in a string.
RIGHT	string1=RIGHT(string2,length)	Returns the rightmost portion of a string.
SHA	string1=SHA(string2)	Returns a 160-bit Secure Hash Algorithm (SHA) checksum for the string.
SHA1	string1=SHA1(string2)	Alias for SHA.
SOUNDEX	string1=SOUNDEX(string2)	Returns the SOUNDEX for a string. In theory, two strings that "sound alike" will have similar SOUNDEX values.
SPACE	spaces=SPACE(count)	Returns the specified number of space characters.
SUBSTRING_INDEX	string1=SUBSTRING_INDEX(string2, delimiter,count)	Returns a string from a character-delimited set of strings.
UNCOMPRESSED_LENGTH	length=UNCOMPRESSED_LENGTH( compressed_string)	Returns the length of a compressed string as if it were decompressed.
UNCOMPRESS	string1=UNCOMPRESS(string2)	Reverses the effect of COMPRESS.
UNHEX	character=UNHEX(HexNumber)	Converts a hexadecimal number to its ASCII equivalent.
UPPER	string1=UPPER(string2)	Converts a string to uppercase. Synonym for UCASE.