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

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

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

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

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

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

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

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.