Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 26.9Mb
PDF (RPM) - 25.3Mb
HTML Download (TGZ) - 6.3Mb
HTML Download (Zip) - 6.3Mb
HTML Download (RPM) - 5.3Mb
Man Pages (TGZ) - 157.6Kb
Man Pages (Zip) - 260.9Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

12.5 String Functions

Table 12.7 String Operators

Name Description
ASCII() Return numeric value of left-most character
BIN() Return a string containing binary representation of a number
BIT_LENGTH() Return length of argument in bits
CHAR() Return the character for each integer passed
CHAR_LENGTH() Return number of characters in argument
CHARACTER_LENGTH() Synonym for CHAR_LENGTH()
CONCAT() Return concatenated string
CONCAT_WS() Return concatenate with separator
ELT() Return string at index number
EXPORT_SET() Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
FIELD() Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET() Return the index position of the first argument within the second argument
FORMAT() Return a number formatted to specified number of decimal places
HEX() Return a hexadecimal representation of a decimal or string value
INSERT() Insert a substring at the specified position up to the specified number of characters
INSTR() Return the index of the first occurrence of substring
LCASE() Synonym for LOWER()
LEFT() Return the leftmost number of characters as specified
LENGTH() Return the length of a string in bytes
LIKE Simple pattern matching
LOAD_FILE() Load the named file
LOCATE() Return the position of the first occurrence of substring
LOWER() Return the argument in lowercase
LPAD() Return the string argument, left-padded with the specified string
LTRIM() Remove leading spaces
MAKE_SET() Return a set of comma-separated strings that have the corresponding bit in bits set
MATCH Perform full-text search
MID() Return a substring starting from the specified position
NOT LIKE Negation of simple pattern matching
NOT REGEXP Negation of REGEXP
OCT() Return a string containing octal representation of a number
OCTET_LENGTH() Synonym for LENGTH()
ORD() Return character code for leftmost character of the argument
POSITION() Synonym for LOCATE()
QUOTE() Escape the argument for use in an SQL statement
REGEXP Whether string matches regular expression
REPEAT() Repeat a string the specified number of times
REPLACE() Replace occurrences of a specified string
REVERSE() Reverse the characters in a string
RIGHT() Return the specified rightmost number of characters
RLIKE Whether string matches regular expression
RPAD() Append string the specified number of times
RTRIM() Remove trailing spaces
SOUNDEX() Return a soundex string
SOUNDS LIKE Compare sounds
SPACE() Return a string of the specified number of spaces
STRCMP() Compare two strings
SUBSTR() Return the substring as specified
SUBSTRING() Return the substring as specified
SUBSTRING_INDEX() Return a substring from a string before the specified number of occurrences of the delimiter
TRIM() Remove leading and trailing spaces
UCASE() Synonym for UPPER()
UNHEX() Return a string containing hex representation of a number
UPPER() Convert to uppercase

String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable. See Section 5.1.1, “Configuring the Server”.

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

For functions that take length arguments, noninteger arguments are rounded to the nearest integer.

  • ASCII(str)

    Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for 8-bit characters.

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

    See also the ORD() function.

  • 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'
  • BIT_LENGTH(str)

    Returns the length of the string str in bits.

    mysql> SELECT BIT_LENGTH('text');
            -> 32
  • CHAR(N,... [USING charset_name])

    CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the 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'

    CHAR() arguments larger than 255 are converted into multiple result bytes. For example, CHAR(256) is equivalent to CHAR(1,0), and CHAR(256*256) is equivalent to CHAR(1,0,0):

    mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
    +----------------+----------------+
    | HEX(CHAR(1,0)) | HEX(CHAR(256)) |
    +----------------+----------------+
    | 0100           | 0100           |
    +----------------+----------------+
    mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));
    +------------------+--------------------+
    | HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |
    +------------------+--------------------+
    | 010000           | 010000             |
    +------------------+--------------------+

    By default, CHAR() returns a binary string. To produce a string in a given character set, use the optional USING clause:

    mysql> SELECT CHARSET(CHAR(X'65')), CHARSET(CHAR(X'65' USING utf8));
    +----------------------+---------------------------------+
    | CHARSET(CHAR(X'65')) | CHARSET(CHAR(X'65' USING utf8)) |
    +----------------------+---------------------------------+
    | binary               | utf8                            |
    +----------------------+---------------------------------+

    If USING is given and the result string is illegal for the given character set, a warning is issued. Also, if strict SQL mode is enabled, the result from CHAR() becomes NULL.

  • CHAR_LENGTH(str)

    Returns the length of the string str, measured in characters. A multibyte character counts as a single character. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

  • CHARACTER_LENGTH(str)

    CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

  • CONCAT(str1,str2,...)

    Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.

    CONCAT() returns NULL if any argument is NULL.

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

    For quoted strings, concatenation can be performed by placing the strings next to each other:

    mysql> SELECT 'My' 'S' 'QL';
            -> 'MySQL'
  • CONCAT_WS(separator,str1,str2,...)

    CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

    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'

    CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

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

    ELT() returns the Nth element of the list of strings: 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, 'Aa', 'Bb', 'Cc', 'Dd');
            -> 'Aa'
    mysql> SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd');
            -> 'Dd'
  • EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

    Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character ,). The number of bits examined is given by number_of_bits, which has a default of 64 if not specified. number_of_bits is silently clipped to 64 if larger than 64. It is treated as an unsigned integer, so a value of −1 is effectively the same as 64.

    mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
            -> 'Y,N,Y,N'
    mysql> SELECT EXPORT_SET(6,'1','0',',',10);
            -> '0,1,1,0,0,0,0,0,0,0'
  • FIELD(str,str1,str2,str3,...)

    Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

    If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.

    If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT().

    mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
            -> 2
    mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
            -> 0
  • FIND_IN_SET(str,strlist)

    Returns a value in the range of 1 to N if the string str is in the string 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 optimized 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 does not work properly if the first argument contains a comma (,) character.

    mysql> SELECT FIND_IN_SET('b','a,b,c,d');
            -> 2
  • FORMAT(X,D[,locale])

    Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.

    The optional third parameter enables a locale to be specified to be used for the result number's decimal point, thousands separator, and grouping between separators. Permissible locale values are the same as the legal values for the lc_time_names system variable (see Section 10.15, “MySQL Server Locale Support”). If no locale is specified, the default is 'en_US'.

    mysql> SELECT FORMAT(12332.123456, 4);
            -> '12,332.1235'
    mysql> SELECT FORMAT(12332.1,4);
            -> '12,332.1000'
    mysql> SELECT FORMAT(12332.2,0);
            -> '12,332'
    mysql> SELECT FORMAT(12332.2,2,'de_DE');
            -> '12.332,20'
  • HEX(str), HEX(N)

    For a string argument str, HEX() returns a hexadecimal string representation of str where each byte of each character in str is converted to two hexadecimal digits. (Multibyte characters therefore become more than two digits.) The inverse of this operation is performed by the UNHEX() function.

    For a numeric argument N, HEX() returns a hexadecimal string representation of the value of N treated as a longlong (BIGINT) number. This is equivalent to CONV(N,10,16). The inverse of this operation is performed by CONV(HEX(N),16,10).

    mysql> SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));
            -> 'abc', 616263, 'abc'
    mysql> SELECT HEX(255), CONV(HEX(255),16,10);
            -> 'FF', 255
  • 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. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.

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

    This function is multibyte safe.

  • 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 order of the arguments is reversed.

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

    This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

  • LCASE(str)

    LCASE() is a synonym for LOWER().

  • LEFT(str,len)

    Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

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

    This function is multibyte safe.

  • LENGTH(str)

    Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

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

    The Length() OpenGIS spatial function is named GLength() in MySQL.

  • LOAD_FILE(file_name)

    Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

    If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

    The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

    mysql> UPDATE t
                SET blob_col=LOAD_FILE('/tmp/picture')
                WHERE id=1;
  • LOCATE(substr,str), LOCATE(substr,str,pos)

    The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax 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. Returns NULL if substr or str is NULL.

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

    This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

  • LOWER(str)

    Returns the string str with all characters changed to lowercase according to the current character set mapping. The default is latin1 (cp1252 West European).

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

    LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a nonbinary string:

    mysql> SET @str = BINARY 'New York';
    mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
    +-------------+-----------------------------------+
    | LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
    +-------------+-----------------------------------+
    | New York    | new york                          |
    +-------------+-----------------------------------+

    This function is multibyte safe.

  • LPAD(str,len,padstr)

    Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

    mysql> SELECT LPAD('hi',4,'??');
            -> '??hi'
    mysql> SELECT LPAD('hi',1,'??');
            -> 'h'
  • LTRIM(str)

    Returns the string str with leading space characters removed.

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

    This function is multibyte safe.

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

    Returns a set value (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, and so on. NULL values 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(1 | 4,'hello','nice',NULL,'world');
            -> 'hello'
    mysql> SELECT MAKE_SET(0,'a','b','c');
            -> ''
  • MID(str,pos,len)

    MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

  • OCT(N)

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

    mysql> SELECT OCT(12);
            -> '14'
  • OCTET_LENGTH(str)

    OCTET_LENGTH() is a synonym for LENGTH().

  • ORD(str)

    If the leftmost character of the string str is a multibyte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:

      (1st byte code)
    + (2nd byte code * 256)
    + (3rd byte code * 256^2) ...

    If the leftmost character is not a multibyte character, ORD() returns the same value as the ASCII() function.

    mysql> SELECT ORD('2');
            -> 50
  • POSITION(substr IN str)

    POSITION(substr IN str) is a synonym for LOCATE(substr,str).

  • 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 enclosed by single quotation marks and with each instance of backslash (\), single quote ('), ASCII NUL, and Control+Z preceded by a backslash. If the argument is NULL, the return value is the word NULL without enclosing single quotation marks.

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

    For comparison, see the quoting rules for literal strings and within the C API in Section 9.1.1, “String Literals”, and Section 23.8.7.53, “mysql_real_escape_string()”.

  • REPEAT(str,count)

    Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL.

    mysql> SELECT REPEAT('MySQL', 3);
            -> 'MySQLMySQLMySQL'
  • REPLACE(str,from_str,to_str)

    Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

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

    This function is multibyte safe.

  • REVERSE(str)

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

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

    This function is multibyte safe.

  • RIGHT(str,len)

    Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

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

    This function is multibyte safe.

  • RPAD(str,len,padstr)

    Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

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

    This function is multibyte safe.

  • RTRIM(str)

    Returns the string str with trailing space characters removed.

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

    This function is multibyte 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 four 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 nonalphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.

    Important

    When using SOUNDEX(), you should be aware of the following limitations:

    • This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.

    • This function is not guaranteed to provide consistent results with strings that use multibyte character sets, including utf-8. See Bug #22638 for more information.

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

    This function implements the original Soundex algorithm, not the more popular enhanced version (also described by D. Knuth). The difference is that original version discards vowels first and duplicates second, whereas the enhanced version discards duplicates first and vowels second.

  • expr1 SOUNDS LIKE expr2

    This is the same as SOUNDEX(expr1) = SOUNDEX(expr2).

  • SPACE(N)

    Returns a string consisting of N space characters.

    mysql> SELECT SPACE(6);
            -> '      '
  • SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)

    SUBSTR() is a synonym for SUBSTRING().

  • SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

    The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

    For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

    mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'
    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'
    mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'
    mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'
    mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'
    mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'

    This function is multibyte safe.

    If len is less than 1, the result is the empty string.

  • 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. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

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

    This function is multibyte safe.

  • TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

    Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if 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 multibyte safe.

  • UCASE(str)

    UCASE() is a synonym for UPPER().

  • UNHEX(str)

    For a string argument str, UNHEX(str) interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.

    mysql> SELECT UNHEX('4D7953514C');
            -> 'MySQL'
    mysql> SELECT X'4D7953514C';
            -> 'MySQL'
    mysql> SELECT UNHEX(HEX('string'));
            -> 'string'
    mysql> SELECT HEX(UNHEX('1267'));
            -> '1267'

    The characters in the argument string must be legal hexadecimal digits: '0' .. '9', 'A' .. 'F', 'a' .. 'f'. If the argument contains any nonhexadecimal digits, the result is NULL:

    mysql> SELECT UNHEX('GG');
    +-------------+
    | UNHEX('GG') |
    +-------------+
    | NULL        |
    +-------------+

    A NULL result can occur if the argument to UNHEX() is a BINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval. For example, '41' is stored into a CHAR(3) column as '41 ' and retrieved as '41' (with the trailing pad space stripped), so UNHEX() for the column value returns 'A'. By contrast '41' is stored into a BINARY(3) column as '41\0' and retrieved as '41\0' (with the trailing pad 0x00 byte not stripped). '\0' is not a legal hexadecimal digit, so UNHEX() for the column value returns NULL.

    For a numeric argument N, the inverse of HEX(N) is not performed by UNHEX(). Use CONV(HEX(N),16,10) instead. See the description of HEX().

  • UPPER(str)

    Returns the string str with all characters changed to uppercase according to the current character set mapping. The default is latin1 (cp1252 West European).

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

    See the description of LOWER() for information that also applies to UPPER(), such as information about how to perform lettercase conversion of binary strings (BINARY, VARBINARY, BLOB) for which these functions are ineffective.

    This function is multibyte safe.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Mike Fehse on September 25, 2010
To expand a word, from an abbreviation to the full spelling, such as 'Ing:' to 'Ingredient(s):', or just inserting something without overwriting everything else, you can use the following:

UPDATE table_name AS t SET t.field_name = INSERT(t.field_name, 4, 0, 'redient(s)') WHERE LEFT(t.field_name, 4) = 'Ing:';

This inserts 'edient(s)' between the 'g' and ':' in 'Ing:', giving us 'Ingredient(s):' It also tests t.field_name to see if it is to be updated. This is based on knowing that the first three charters in t.field_name will be 'Ing:' or not, and if it is then we spell it out. You can even expand the spelling in the t.field_name, not just the start or end of it, as this might suggest. Use INSTR(t.field_name, 'str_to_expand'), so it would end up looking like:

UPDATE table_name AS t SET t.field_name = INSERT(t.field_name, INSTR(t.field_name, 'Ing:'), 0, 'redient(s)') WHERE LEFT(t.field_name, 4) = 'Ing:';

If you know that you have the abbreviation in more than just one place within a field (aka column) then just run the command again. In both cases the number zero '0' is the key, it tells the INSERT command not to overwrite any of the following charters, just insert the requested sub-string.
  Posted by Benjamin Bouraada on October 27, 2010
Stringcutting:

PROCEDURE Insert(IN STR_IN VARCHAR(255), IN ID_IN INT)
BEGIN
declare lang int default LENGTH(STR_IN);
declare sep char default ';';
declare tmpMerge char(255);
declare tmpChar char;
declare loop_done integer default 1;
stringcutter: loop
set tmpChar = substring(STR_IN,loop_done,1);
if tmpChar <> sep then
if isnull(tmpMerge) then set tmpMerge = tmpChar;
else select concat(tmpMerge,tmpChar)into tmpMerge;end if;end if;
if tmpChar = sep then DO WHAT YOU WANT;set tmpMerge = NULL;end if;
set loop_done = loop_done + 1;
if loop_done = lang then leave stringcutter;end if;
end loop stringcutter;
END
  Posted by Yerkebulan Kuryshbayev on January 24, 2011
my mysql split function:

DROP PROCEDURE IF EXISTS `SPLIT_STRING`;

DELIMITER |
CREATE PROCEDURE `SPLIT_STRING` (IN `MY_STRING` TEXT, IN `MY_DELIMITER` TEXT)
LANGUAGE SQL
SQL SECURITY INVOKER
BEGIN
#-------------------------------------------------------------------------------
IF NOT ISNULL(MY_STRING) THEN
IF NOT ISNULL(MY_DELIMITER) THEN
#
SET @SS = TRIM(MY_STRING);
SET @DEL = TRIM(MY_DELIMITER);
#
IF LENGTH(@SS) > 0 THEN
IF LENGTH(@DEL) > 0 THEN
#
SET @DP = (SELECT LOCATE(@DEL, @SS, 1));
IF @DP > 0 THEN
#------------------------CREATE TEMP TABLE-----------------------
DROP TABLE IF EXISTS `TEMPORARY_TABLE_OF_SPLIT_STRINGS`;
#
CREATE TEMPORARY TABLE `TEMPORARY_TABLE_OF_SPLIT_STRINGS` (
`SUB_STRING` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
)
ENGINE=INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci ;
#----------------------------------------------------------------
SET @SS_2 = @SS;
#
REPEAT
#
SET @FIRST_ELEMENT = (SELECT SUBSTRING_INDEX(@SS_2, @DEL, 1));
SET @SS_2 = (SELECT TRIM(LEADING CONCAT(@FIRST_ELEMENT, @DEL) FROM @SS_2));
#
INSERT INTO `TEMPORARY_TABLE_OF_SPLIT_STRINGS` (`SUB_STRING`) VALUES (@FIRST_ELEMENT);
SET @DP = (SELECT LOCATE(@DEL, @SS, @DP + 1));
#
IF @DP = 0 THEN
SET @LAST_ELEMENT = (SELECT SUBSTRING_INDEX(@SS_2, @DEL, -1));
INSERT INTO `TEMPORARY_TABLE_OF_SPLIT_STRINGS` (`SUB_STRING`) VALUES (@LAST_ELEMENT);
END IF;
UNTIL @DP = 0
END REPEAT;
#
SELECT * FROM TEMPORARY_TABLE_OF_SPLIT_STRINGS;
#----------------------------------------------------------------
DROP TABLE IF EXISTS `TEMPORARY_TABLE_OF_SPLIT_STRINGS`;
#----------------------------------------------------------------
ELSE
SELECT NULL;
END IF;
ELSE
SELECT NULL;
END IF;
ELSE
SELECT NULL;
END IF;
ELSE
SELECT NULL;
END IF;
ELSE
SELECT NULL;
END IF;
END; |
DELIMITER ;
  Posted by Juan Andrés Calleja on February 14, 2011
Hi, I found one usefully example in mysql forums (http://lists.mysql.com/mysql/199134) for split a varchar to use in the 'IN' clause in query. I create new input parameter with table name for use in dynamic CREATE table statement.

Thus split function can be used more than once in the same stored procedure because table have any name.

This is the SP. Excuse me for my bad english :(

DELIMITER $$

CREATE PROCEDURE `SplitString`( IN input TEXT,
IN delimiter VARCHAR(10), IN Table_name VARCHAR(50)
)
SQL SECURITY INVOKER
BEGIN
DECLARE cur_position INT DEFAULT 1 ;
DECLARE remainder TEXT;
DECLARE cur_string VARCHAR(1000);
DECLARE delimiter_length TINYINT UNSIGNED;


set @sql_drop = concat('DROP TEMPORARY TABLE IF EXISTS ',' ',Table_name);
prepare st_drop from @sql_drop;
execute st_drop;

set @sql_create = concat('CREATE TEMPORARY TABLE ' ,Table_name ,' (value VARCHAR(1000) NOT NULL PRIMARY KEY) ENGINE=MEMORY;');
prepare st_create from @sql_create;
execute st_create;

SET remainder = input;
SET delimiter_length = CHAR_LENGTH(delimiter);

WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
SET cur_position = INSTR(remainder, delimiter);

IF cur_position = 0 THEN
SET cur_string = remainder;
ELSE
SET cur_string = LEFT(remainder, cur_position - 1);
END IF;

IF TRIM(cur_string) != '' THEN
set @sql_insert = concat('INSERT INTO ' ,Table_name, ' VALUES (',cur_string,');');
prepare st_insert from @sql_insert;
execute st_insert;
END IF;

SET remainder = SUBSTRING(remainder, cur_position +
delimiter_length);
END WHILE;
END

  Posted by shiva Kumar on February 17, 2011
Hi mysqlis,

Here the difference between the string functions LOCATE and FIND_IN_SET is..

1.When using LOCATE for integers use carefully..

example:
If I need to return 1 if 2 is in the set '1,2,3,4,5'.

SELECT IF(LOCATE(2,'1,2,3,4,5,6,7,8,9')>0,1,0);
You know very well it return 1,because the set contains value 2 in given set.
SO it is no problem...

FOR this Example query it returns wrong as we expected...

SELECT IF(LOCATE(2,'11,12,3,4,5,6,7,8,9')>0,1,0);
even though 2 is not available in set,it gives 1.

here LOCATE function takes the set as the STRING not the comma(,) separated value..

In this situation Please use the FIND_IN_SET - which is great function for the comma(,) separated value set.

Now,

SELECT IF(FIND_IN_SET(2,'11,12,3,4,5,6,7,8,9')>0,1,0);

It returns 1 as we expected...

Note:
1.Use LOCATE function for alphabetic strings only..
2.And also use LOCATE for numeric numbers that set contains the numbers only 0,1,2,3,4,5,6,7,8,9

i.e.,

SELECT IF(LOCATE(input,'0,1,2,3,4,5,6,7,8,9')>0,1,0);

input must be any one within 0,1,2,3,4,5,6,7,8,9
Its work nicely.

  Posted by Vector Thorn on April 9, 2011
Hi guys. For those of you who are trying to do a string replace on a string that contains a wildcard, you can actually write your own UDF to solve that.
  Posted by Lies DJILLALI on August 31, 2011
Thank you Giovanni for your strip_tags function,

Here is a patched version because Mysql crashed when I tryied to proceed a NULL value

delimiter ||

DROP FUNCTION IF EXISTS strip_tags||
CREATE FUNCTION strip_tags( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE sstart INT UNSIGNED;
DECLARE ends INT UNSIGNED;
IF x IS NOT NULL THEN
SET sstart = LOCATE('<', x, 1);
REPEAT
SET ends = LOCATE('>', x, sstart);
SET x = CONCAT(SUBSTRING( x, 1 ,sstart -1) ,SUBSTRING(x, ends +1 )) ;
SET sstart = LOCATE('<', x, 1);
UNTIL sstart < 1 END REPEAT;
END IF;
return x;
END;
||
delimiter ;

mysql> SELECT strip_tags('<a href="HelloWorld.html"><B>Hi, mate!</B></a>') as strip_tags;

+------------+
| strip_tags |
+------------+
| Hi, mate! |
+------------+
  Posted by Steven Gath on October 31, 2011
I was looking for an initcap or ucfirst function that would uppercase the first letter of each word in a string and couldn't find one so I modified one of the functions here so it worked for any number of words. Hope it can help someone else.

DELIMITER $$
DROP FUNCTION IF EXISTS `initcap`$$
CREATE FUNCTION `initcap`(x varchar(255)) RETURNS varchar(255) CHARSET utf8
DETERMINISTIC
begin
set @out_str='';
set @l_str='';
set @r_str='';

set @pos=LOCATE(' ',x);
SELECT x into @r_str;
while (@pos > 0) DO
SELECT SUBSTRING(@r_str,1,@pos-1) into @l_str;
SELECT SUBSTRING(@r_str,@pos+1) into @r_str;
SELECT concat(@out_str,upper(substring(@l_str,1,1)),lower(substring(@l_str,2)),' ') into @out_str;
set @pos=LOCATE(' ',@r_str);
END WHILE;
SELECT concat(@out_str,upper(substring(@r_str,1,1)),lower(substring(@r_str,2))) into @out_str;
return trim(@out_str);
end$$

DELIMITER ;
  Posted by Paul Caskey on November 10, 2011
For DATE and DATETIME operations, MySQL demands the year be first, then month, then day. In the USA, a common date format is month-day-year . This example converts a date from MM-DD-YYYY format to MySQL's preferred YYYY-MM-DD. It also works on M-D-YY input, or other shortcut forms. Change the '-' separate to '.' or whatever you need. This takes 6-13-2011 and returns a STRING of '2011-6-13':

SELECT CONCAT(SUBSTRING_INDEX(DateMDY, '-', -1), '-', SUBSTRING_INDEX(DateMDY, '-', 2))

Now you can CAST this to a DATE, and then it will ORDER BY or GROUP BY properly. E.g. this takes '11.1.2011' and returns a real DATE of 2011-11-01. As usual I'm sure there are other ways to do this. I was just happy to figure this out without resorting to PHP or Perl.

CAST(CONCAT(SUBSTRING_INDEX(DateMDY, '.', -1), '.', SUBSTRING_INDEX(DateMDY, '.', 2)) AS DATE)

  Posted by Manish Singh on November 17, 2011
If column of a table contains JSON string, then we can extract the value corresponding to a json key:

select substring_index(substring_index(json_params_column, 'jsonKey":"', -1), '"', 1) from MyTable;
  Posted by Jesse Perring on December 16, 2011
To split a field out into one row for each separator in the field (i.e. if you have a field with a variable number of comma separated values in each row, but you want a table with a row for every value in that list):

The table I started with was something like...
id, list_of_types
(champion_1), (mage, fighter, support)
(champion_2), (support, mage)
(champion_3), (tank, support)

and the table I needed was....
id, type
champion_1, mage
champion_1, fighter
champion_1, support
champion_2, support
champion_3, tank
champion_3, support

So I ran...

select tOut.*, replace(substring(substring_index(type, ',', ocur_rank), length(substring_index(type, ',', ocur_rank - 1)) + 1), ',', '')
from (select @num_type := if(@id_check = tY.id, @num_type + 1, 1) as ocur_rank, @id_check := tY.id as id_check, tY.*
from (select LENGTH(list) - length(replace(list, ',', '')) as num_ocur, id, list from item) tX
inner join (select LENGTH(list) - length(replace(list, ',', '')) as num_ocur, id, list from item) tY
inner join (select @num_type := 0, @id_check := 'some_id') tZ) tOut
where ocur_rank <= num_ocur + 1;

Where "id" is just some unique identifier for each field you're splitting up and "list" is the list of separated values. The thought behind the query is to just join a table to itself, rank the rows for each id, then only show rows where the rank is less than the number of occurrences of the separator in the list you're splitting up. The outter most select then shows the value in between the rank and rank + 1 occurrence of the separator in the list.

This may not work if some of the lists don't have any occurrence of the separator.

  Posted by Joseph Edmonds on January 19, 2012
Note concat can be used to compare two columns in the same query

so if you want to check if the contents of one column are in another column

http://www.edmondscommerce.co.uk/mysql/compare-two-columns-in-mysql/
  Posted by halászsándor halászsándor on February 13, 2012
I had the same problem that Edmonds described, and for that I used this expression:

LOCATE(col1, col2) > 0 -- if "col1" is found in "col2"
OR
LOCATE(col2, col1) > 0 -- if "col2" is found in "col1"
  Posted by Jez Gomez on April 18, 2012
I wanted to get the first portion of an email address, before the @ (ie, left of the @):

SELECT SUBSTRING_INDEX(email,'@',1) FROM <table> WHERE email REGEXP '@'
  Posted by Mariano Otero on June 22, 2012
MySQL does not include a function to split a delimited string. However, it’s very easy to create your own function. I've added an example here: http://www.indumental.com/in/software/mysql.html. If you follow the example on that page, you'll be able to do something like this:

SELECT SPLIT_STR('a|bb|ccc|dd', '|', 3) as third;

-Mo

  Posted by Alexandre Nunes on July 6, 2012
Suppose you have a text column with some delimiter separated data, and you want to get tokens from it, i.e. like strtok() would.

Ex, for the text "0,1,3,5,6", you wanna get the third element. This would do the trick:

select substring_index(substring_index(column,',',3),',',-1) as third;

P.S..: This seems a simplification of the previous example (which I managed to miss)
  Posted by die manto on February 7, 2013
Tip to compare two columns:

SELECT *
FROM
`table`
WHERE
`col1` LIKE CONCAT('%', `col2`, '%')
OR col2 LIKE CONCAT('%',`col1`,'%')

Posted by http://www.competenciaperfecta.com/

  Posted by Gunter Sammet on April 5, 2013
I was looking for a PHP equivalent for strpos and strrpos to get in combination with substring the string after the last occurrence of a character. Have a look at MYSQL SUBSTRING_INDEX. It may does what you want.
One of the first search results on another website suggested to use the locate function and if you need the last occurrence, use reverse string before using locate. Then use reverse again to get the correct sting. Just thought I'll post here with the important keywords, so it shows up in search results in case somebody else is looking for the same. HTH
  Posted by Biniam Kefale on May 31, 2013
Example of using CONCAT() and UPDATE

This code updates the column named 'phone_number' in the table called 'user' by concatenating '0' in front of the new phone_number.

The new phone_number is old phone_number minus the first 4 characters or beginning from the 5th character.

The update will only be applied to the records with id between 3 and 30 exclusive.

UPDATE user SET phone_number = CONCAT('0', SUBSTRING(phone_number, 5)) WHERE id > 3 AND id < 30;

See how the code is shorter than the explanation?

Biniam from Ethiopia.
  Posted by Adrian Humphreys on February 22, 2014
Here's a function to Propercase all the words in a string:

CREATE FUNCTION fn_propercase
(p_string Varchar(254)) RETURNS Varchar
BEGIN
SET @out="";
SET @x = 1;
SET @len = LENGTH(p_string);
/* Always make the 1st char uppercase. Set the flag on. */
SET @uc = 1;

REPEAT
SET @char= MID(p_string,@x,1);
IF @uc=1 THEN
SET @out= CONCAT(@out,UPPER(@char));
ELSE
SET @out= CONCAT(@out,LOWER(@char)) ;
END IF;

/* AFTER the char is written, test whether it is blank
if so, the NEXT char will be uppercase. */
IF @char=" " THEN
SET @uc= 1;
ELSE
SET @uc= 0;
END IF;

SET @x= @x + 1;
UNTIL @x > @len END REPEAT;

RETURN @out;

END
  Posted by Shivakumar Durg on August 7, 2014
The following formula can be used to extract the Nth item in a delimited list.

SET @str:='mba,bca,mca,Bed'; # Your Complete String
SET @length:=LENGTH(@str);
SET @limit:=@length-LENGTH(REPLACE(@str,',',''))+1;
SET @pos:=2; # Substring position value
SET @tmp:=REVERSE(SUBSTRING_INDEX(@str,',',@pos));
SELECT IF(@limit>=@pos,
IF(@pos=1,SUBSTRING_INDEX(@str,',',@pos),REVERSE(SUBSTRING(@tmp,1,LOCATE(',',@tmp)-1))),'Not Exist')AS "Required_String"
  Posted by EE Durham on August 10, 2014
Many thanks to Adrian Humphreys for his Propercase / Titlecase example: great idea :) Couldn't get it to work as-is, but here is a working version :)
Cheers!

DROP FUNCTION IF EXISTS str_titlecase;
CREATE FUNCTION str_titlecase (p_string varchar(254)) RETURNS varchar (254)
## by Adrian Humphreys, edited by Durham
## for given string, concatenate Capitalized first letter of
## each given word with Lower Case remainder of word
BEGIN
DECLARE output_string VARCHAR(254) DEFAULT '';
DECLARE temp_string VARCHAR(254);
DECLARE x INT DEFAULT 1; /*tracking variable*/
DECLARE uc INT DEFAULT 1; /*uppercase flag*/
DECLARE input_string_length INT DEFAULT LENGTH(p_string);

IF p_string IS NOT NULL THEN
REPEAT
SET temp_string := MID(p_string,x,1);
IF uc=1 THEN
SET output_string := CONCAT(output_string,UPPER(temp_string));
ELSE
SET output_string := CONCAT(output_string,LOWER(temp_string)) ;
END IF;

/* AFTER the char is written, test whether it is blank
if so, the NEXT char will be uppercase. */
IF temp_string=' ' THEN
SET uc := 1;
ELSE
SET uc := 0;
END IF;

SET x := x + 1;
UNTIL x > input_string_length END REPEAT;
END IF;

RETURN output_string;

END

#########################
## Usage (all lower case input):
select str_titlecase('i am a cat') as title from dual;

## Results:
title
------
I Am A Cat

## Usage (blank string case):
select str_titlecase('') as title from dual;

## Results:
title
------

## Usage (all upper case input):
select str_titlecase('I AM A DOLPHIN') as title from dual;

## Results:
title
------
I Am A Dolphin

## Usage (mixed-case input):
select str_titlecase('I am THE PRODUCT of your IMAGinatioN') as title from dual;

## Results:
title
------
I Am The Product Of Your Imagination

  Posted by Luis Rocha on March 27, 2015
Found this http://stackoverflow.com/a/18218191 but it would not work correctly. So I wrote my own function based on that. You might want to filter the results a bit before calling this function (i.e. "SELECT * FROM abc WHERE haystack LIKE '%needle%' AND hasString(haystack, 'needle')..."). It runs rather slow otherwise.

CREATE FUNCTION `hasString`(haystack TINYTEXT, needle TINYTEXT) RETURNS TINYINT(1)
BEGIN
DECLARE needleFound TINYINT(1);
DECLARE inipos INTEGER;
DECLARE endpos INTEGER;
DECLARE maxlen INTEGER;
DECLARE item VARCHAR(100);
DECLARE delim VARCHAR(1);

SET delim = ',';
SET inipos = 1;
SET endpos = 0;
SET needleFound = 0;
SET maxlen = LENGTH(haystack);

REPEAT
SET endpos = LOCATE(delim, haystack, inipos);
SET item = SUBSTR(haystack, inipos, endpos - inipos);

IF inipos = 1 AND endpos = 0 THEN
IF haystack = needle THEN
SET needleFound = 1;
END IF;
SET endpos = maxlen + 100;
ELSE
IF inipos > 1 AND endpos = 0 THEN
SET endpos = maxlen + 50;
ELSE
IF item <> '' AND item IS NOT NULL THEN
IF item=needle THEN
SET needleFound = 1;
END IF;
ELSE
SET inipos = maxlen + 10;
END IF;
END IF;
END IF;
SET inipos = endpos + 1;
UNTIL inipos >= maxlen END REPEAT;

RETURN needleFound;
END
  Posted by Jens Walte on April 29, 2015
fastest split() function

/**
* #1: this way is 10-20% faster than #3 and supports not included indexes otherwise than #2
*
* @example: split('a|bbb|cc', '|', 0) -> 'a'
* @example: split('a|bbb|cc', '|', 1) -> 'bbb'
* @example: split('a|bbb|cc', '|', 2) -> 'cc'
* @example: split('a|bbb|cc', '|', 3) -> ''
*/
substring_index(substring_index(concat(content, delimiter), delimiter, index+1), delimiter, -1);

/**
* #2: faster than #3, but not included index will return last entry
*
* @see: Posted by Mariano Otero on June 22 2012 3:43pm
* @example: split('a|bbb|cc', '|', 0) -> 'a'
* @example: split('a|bbb|cc', '|', 1) -> 'bbb'
* @example: split('a|bbb|cc', '|', 2) -> 'cc'
* @example: split('a|bbb|cc', '|', 3) -> 'cc' (unexpected)
*/
substring_index(substring_index(content, delimiter, index+1), delimiter, -1);

/**
* #3: first introduced split example, supports not included indexes
*
* @see: Posted by Bob Collins on March 17 2006 8:56pm
* @example: split('a|bbb|cc', '|', 0) -> 'a'
* @example: split('a|bbb|cc', '|', 1) -> 'bbb'
* @example: split('a|bbb|cc', '|', 2) -> 'cc'
* @example: split('a|bbb|cc', '|', 3) -> ''
*/
replace(substring(substring_index(content, delimiter, index+1), length(substring_index(content, delimiter, index)) + 1), delimiter, '');

Sign Up Login You must be logged in to post a comment.