GNU Info

(mysql.info)String functions

---

Next: Numeric Functions Prev: Non-typed Operators Up: Functions

---

Enter node , (file) or (file)node

String Functions
----------------

String-valued functions return `NULL' if the length of the result would
be greater than the `max_allowed_packet' server parameter.  Note:
Server parameters.

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

`ASCII(str)'
     Returns the ASCII code value of the leftmost character of the
     string `str'. Returns `0' if `str' is the empty string.  Returns
     `NULL' if `str' is `NULL':

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

     See also the `ORD()' function.

`ORD(str)'
     If the leftmost character of the string str is a multi-byte
     character, returns the code of multi-byte character by returning
     the ASCII code value of the character in the format of: `((first
     byte ASCII code)*256+(second byte ASCII code))[*256+third byte
     ASCII code...]'.  If the leftmost character is not a multi-byte
     character, returns the same value as the like `ASCII()' function
     does:

          mysql> select ORD('2');
                  -> 50

`CONV(N,from_base,to_base)'
     Converts numbers between different number bases.  Returns a string
     representation of the number `N', converted from base `from_base'
     to base `to_base'.  Returns `NULL' if any argument is `NULL'.  The
     argument `N' is interpreted as an integer, but may be specified as
     an integer or a string.  The minimum base is `2' and the maximum
     base is `36'.  If `to_base' is a negative number, `N' is regarded
     as a signed number.  Otherwise, `N' is treated as unsigned.
     `CONV' works with 64-bit precision:

          mysql> select CONV("a",16,2);
                  -> '1010'
          mysql> select CONV("6E",18,8);
                  -> '172'
          mysql> select CONV(-17,10,-18);
                  -> '-H'
          mysql> select CONV(10+"10"+'10'+0xa,10,10);
                  -> '40'

`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'

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

          mysql> select OCT(12);
                  -> '14'

`HEX(N)'
     Returns a string representation of the hexadecimal value of `N',
     where `N' is a longlong (`BIGINT') number.  This is equivalent to
     `CONV(N,10,16)'.  Returns `NULL' if `N' is `NULL':

          mysql> select HEX(255);
                  -> 'FF'

`CHAR(N,...)'
     `CHAR()' interprets the arguments as integers and returns a string
     consisting of the characters given by the ASCII 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'

`CONCAT(str1,str2,...)'
     Returns the string that results from concatenating the arguments.
     Returns `NULL' if any argument is `NULL'.  May have more than 2
     arguments.  A numeric argument is converted to the equivalent
     string form:

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

`CONCAT_WS(separator, str1, str2,...)'
     `CONCAT_WS()' stands for CONCAT With Separator and is a special
     form of `CONCAT()'.  The first argument is the separator for the
     rest of the arguments. The separator can be a string as well as
     the rest of the arguments. If the separator is `NULL', the result
     will be `NULL'.  The function will skip any `NULL's and empty
     strings, after the separator argument. The separator will be added
     between the strings to be concatenated:

          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'

`LENGTH(str)'
`OCTET_LENGTH(str)'
`CHAR_LENGTH(str)'
`CHARACTER_LENGTH(str)'
     Returns the length of the string `str':

          mysql> select LENGTH('text');
                  -> 4
          mysql> select OCTET_LENGTH('text');
                  -> 4

     Note that for `CHAR_LENGTH()', multi-byte characters are only
     counted once.

`LOCATE(substr,str)'
`POSITION(substr IN str)'
     Returns the position of the first occurrence of substring `substr'
     in string `str'. Returns `0' if `substr' is not in `str':

          mysql> select LOCATE('bar', 'foobarbar');
                  -> 4
          mysql> select LOCATE('xbar', 'foobar');
                  -> 0

     This function is multi-byte safe.

`LOCATE(substr,str,pos)'
     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':

          mysql> select LOCATE('bar', 'foobarbar',5);
                  -> 7

     This function is multi-byte 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 arguments are swapped:

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

     This function is multi-byte safe.

`LPAD(str,len,padstr)'
     Returns the string `str', left-padded with the string `padstr'
     until `str' is `len' characters long. If `str' is longer than
     `len'' then it will be shortened to `len' characters.

          mysql> select LPAD('hi',4,'??');
                  -> '??hi'

`RPAD(str,len,padstr)'
     Returns the string `str', right-padded with the string `padstr'
     until `str' is `len' characters long.  If `str' is longer than
     `len'' then it will be shortened to `len' characters.

          mysql> select RPAD('hi',5,'?');
                  -> 'hi???'

`LEFT(str,len)'
     Returns the leftmost `len' characters from the string `str':

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

     This function is multi-byte safe.

`RIGHT(str,len)'
     Returns the rightmost `len' characters from the string `str':

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

     This function is multi-byte safe.

`SUBSTRING(str,pos,len)'
`SUBSTRING(str FROM pos FOR len)'
`MID(str,pos,len)'
     Returns a substring `len' characters long from string `str',
     starting at position `pos'.  The variant form that uses `FROM' is
     ANSI SQL92 syntax:

          mysql> select SUBSTRING('Quadratically',5,6);
                  -> 'ratica'

     This function is multi-byte safe.

`SUBSTRING(str,pos)'

`SUBSTRING(str FROM pos)'
     Returns a substring from string `str' starting at position `pos':

          mysql> select SUBSTRING('Quadratically',5);
                  -> 'ratically'
          mysql> select SUBSTRING('foobarbar' FROM 4);
                  -> 'barbar'

     This function is multi-byte safe.

`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:

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

     This function is multi-byte safe.

`LTRIM(str)'
     Returns the string `str' with leading space characters removed:

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

`RTRIM(str)'
     Returns the string `str' with trailing space characters removed:

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

     This function is multi-byte safe.

`TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)'
     Returns the string `str' with all `remstr' prefixes and/or suffixes
     removed. If none of the specifiers `BOTH', `LEADING' or `TRAILING'
     are given, `BOTH' is assumed. If `remstr' is 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 multi-byte 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 4 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 non-alphanumeric
     characters are ignored in the given string. All international
     alpha characters outside the A-Z range are treated as vowels:

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

`SPACE(N)'
     Returns a string consisting of `N' space characters:

          mysql> select SPACE(6);
                  -> '      '

`REPLACE(str,from_str,to_str)'
     Returns the string `str' with all all occurrences of the string
     `from_str' replaced by the string `to_str':

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

     This function is multi-byte safe.

`REPEAT(str,count)'
     Returns a string consisting of the string `str' repeated `count'
     times. If `count <= 0', returns an empty string. Returns `NULL' if
     `str' or `count' are `NULL':

          mysql> select REPEAT('MySQL', 3);
                  -> 'MySQLMySQLMySQL'

`REVERSE(str)'
     Returns the string `str' with the order of the characters reversed:

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

     This function is multi-byte safe.

`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':

          mysql> select INSERT('Quadratic', 3, 4, 'What');
                  -> 'QuWhattic'

     This function is multi-byte safe.

`ELT(N,str1,str2,str3,...)'
     Returns `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, 'ej', 'Heja', 'hej', 'foo');
                  -> 'ej'
          mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo');
                  -> 'foo'

`FIELD(str,str1,str2,str3,...)'
     Returns the index of `str' in the `str1', `str2', `str3', `...'
     list.  Returns `0' if `str' is not found.  `FIELD()' is the
     complement of `ELT()':

          mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
                  -> 2
          mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
                  -> 0

`FIND_IN_SET(str,strlist)'
     Returns a value `1' to `N' if the string `str' is in the 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 will not work properly if the first
     argument contains a `,':

          mysql> SELECT FIND_IN_SET('b','a,b,c,d');
                  -> 2

`MAKE_SET(bits,str1,str2,...)'
     Returns a set (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,
     etc.  `NULL' strings 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(0,'a','b','c');
                  -> ''

`EXPORT_SET(bits,on,off,[separator,[number_of_bits]])'
     Returns a string where for every bit set in 'bit', you get an 'on'
     string and for every reset bit you get an 'off' string. Each
     string is separated with 'separator' (default ',') and only
     'number_of_bits' (default 64) of 'bits' is used:

          mysql> select EXPORT_SET(5,'Y','N',',',4)
                  -> Y,N,Y,N

`LCASE(str)'
`LOWER(str)'
     Returns the string `str' with all characters changed to lowercase
     according to the current character set mapping (the default is
     ISO-8859-1 Latin1):

          mysql> select LCASE('QUADRATICALLY');
                  -> 'quadratically'

     This function is multi-byte safe.

`UCASE(str)'
`UPPER(str)'
     Returns the string `str' with all characters changed to uppercase
     according to the current character set mapping (the default is
     ISO-8859-1 Latin1):

          mysql> select UCASE('Hej');
                  -> 'HEJ'

     This function is multi-byte safe.

`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 due to one of the above
     reasons, the function returns `NULL':

          mysql> UPDATE table_name
                     SET blob_column=LOAD_FILE("/tmp/picture")
                     WHERE id=1;

If you are not using MySQL Version 3.23, you have to do the reading of
the file inside your application and create an `INSERT' statement to
update the database with the file information. One way to do this, if
you are using the MySQL++ library, can be found at
`http://www.mysql.com/documentation/mysql++/mysql++-examples.html'.

MySQL automatically converts numbers to strings as necessary, and
vice-versa:

     mysql> SELECT 1+"1";
             -> 2
     mysql> SELECT CONCAT(2,' test');
             -> '2 test'

If you want to convert a number to a string explicitly, pass it as the
argument to `CONCAT()'.

If a string function is given a binary string as an argument, the
resulting string is also a binary string.  A number converted to a
string is treated as a binary string.  This only affects comparisons.

String comparison functions

String Comparison Functions

Case Sensitivity Operators

Case Sensitivity