DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) String functions

Info Catalog (mysql.info.gz) Control flow functions (mysql.info.gz) Functions (mysql.info.gz) Numeric Functions
 
 12.3 String Functions
 =====================
 
 String-valued functions return `NULL' if the length of the result would
 be greater than the value of the `max_allowed_packet' system variable.
  Server parameters.
 
 For functions that operate on string positions, the first position is
 numbered 1.
 
 `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 characters with numeric values
      from `0' to `255'.
 
           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
 
      `BIT_LENGTH()' was added in MySQL 4.0.2.
 
 `CHAR(N,...)'
      `CHAR()' interprets the arguments as integers 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_LENGTH(STR)'
      Returns the length of the string STR, measured in characters.  A
      multi-byte character counts as a single character.  This means
      that for a string containing five two-byte characters, `LENGTH()'
      returns `10', whereas `CHAR_LENGTH()' returns `5'.
 
 `CHARACTER_LENGTH(STR)'
      `CHARACTER_LENGTH()' is a synonym for `CHAR_LENGTH()'.
 
 `COMPRESS(STRING_TO_COMPRESS)'
      Compresses a string. This function requires MySQL to have been
      compiled with a compression library such as `zlib'. Otherwise, the
      return value is always `NULL'. The compressed string can be
      uncompressed with `UNCOMPRESS()'.
 
           mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
                   -> 21
           mysql> SELECT LENGTH(COMPRESS(''));
                   -> 0
           mysql> SELECT LENGTH(COMPRESS('a'));
                   -> 13
           mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));
                   -> 15
 
      The compressed string contents are stored the following way:
         * Empty strings are stored as empty strings.
 
         * Non-empty strings are stored as a four-byte length of the
           uncompressed string (low byte first), followed by the
           compressed string.  If the string ends with space, an extra
           `.' character is added to avoid problems with endspace
           trimming should the result be stored in a `CHAR' or `VARCHAR'
           column.  (Use of `CHAR' or `VARCHAR' to store compressed
           strings is not recommended. It is better to use a `BLOB'
           column instead.)
 
      `COMPRESS()' was added in MySQL 4.1.1.
 
 `CONCAT(STR1,STR2,...)'
      Returns the string that results from concatenating the arguments.
      Returns `NULL' if any argument is `NULL'.  May have one or more
      arguments.  If all arguments are non-binary strings, the result is
      a non-binary string.  If the arguments include any binary strings,
      the result is a binary string.  A numeric argument is converted to
      its equivalent binary 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 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'.
      The function skips any `NULL' values after the separator argument.
 
           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'
 
      Before MySQL 4.0.14, `CONCAT_WS()' skips empty strings as well as
      `NULL' values.
 
 `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'
 
 `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'
 
 `EXPORT_SET(BITS,ON,OFF[,SEPARATOR[,NUMBER_OF_BITS]])'
      Returns a string in which for every bit set in the value BITS, you
      get an ON string and for every reset bit 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 (default `,'). The number
      of bits examined is given by NUMBER_OF_BITS (default 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 of STR in the STR1, STR2, STR3, `...' list.
      Returns `0' if STR is not found.  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('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 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 will not work properly if the first argument
      contains a comma (`,') character.
 
           mysql> SELECT FIND_IN_SET('b','a,b,c,d');
                   -> 2
 
 `HEX(N_OR_S)'
      If N_OR_S is a number, 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)'.
 
      From MySQL 4.0.1 and up, if N_OR_S is a string, returns a
      hexadecimal string of N_OR_S where each character in N_OR_S is
      converted to two hexadecimal digits.
 
           mysql> SELECT HEX(255);
                   -> 'FF'
           mysql> SELECT 0x616263;
                   -> 'abc'
           mysql> SELECT HEX('abc');
                   -> 616263
 
 `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 is 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 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.  In MySQL 3.23, this function is
      case sensitive. For 4.0 on, it is case sensitive only if either
      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.
 
           mysql> SELECT LEFT('foobarbar', 5);
                   -> 'fooba'
 
 `LENGTH(STR)'
      Returns the length of the string STR, measured in bytes.  A
      multi-byte character counts as multiple bytes.  This means that
      for a string containing five two-byte characters, `LENGTH()'
      returns `10', whereas `CHAR_LENGTH()' returns `5'.
 
           mysql> SELECT LENGTH('text');
                   -> 4
 
 `LOAD_FILE(FILE_NAME)'
      Reads the file and returns the file contents as a string.  The file
      must be located 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' bytes.
 
      If the file doesn't exist or cannot be read because one of the
      preceding conditions is not satisfied, the function returns `NULL'.
 
           mysql> UPDATE TBL_NAME
                      SET BLOB_COLUMN=LOAD_FILE('/tmp/picture')
                      WHERE id=1;
 
      Before MySQL 3.23, you must read the file inside your application
      and create an `INSERT' statement to update the database with the
      file contents.  If you are using the MySQL++ library, one way to
      do this can be found in the MySQL++ manual, available at
      `http://dev.mysql.com/doc/'.
 
 `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.
 
           mysql> SELECT LOCATE('bar', 'foobarbar');
                   -> 4
           mysql> SELECT LOCATE('xbar', 'foobar');
                   -> 0
           mysql> SELECT LOCATE('bar', 'foobarbar',5);
                   -> 7
 
      This function is multi-byte safe.  In MySQL 3.23, this function is
      case sensitive. For 4.0 on, it is case sensitive only if either
      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
      ISO-8859-1 Latin1).
 
           mysql> SELECT LOWER('QUADRATICALLY');
                   -> 'quadratically'
 
      This function is multi-byte 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 multi-byte 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 multi-byte
      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 multi-byte 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
      surrounded by single quotes and with each instance of single quote
      (`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a
      backslash.  If the argument is `NULL', the return value is the
      word "NULL" without surrounding single quotes.  The `QUOTE()'
      function was added in MySQL 4.0.3.
 
           mysql> SELECT QUOTE('Don\'t!');
                   -> 'Don\'t!'
           mysql> SELECT QUOTE(NULL);
                   -> NULL
 
 `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'
 
 `REPLACE(STR,FROM_STR,TO_STR)'
      Returns the string STR with 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.
 
 `REVERSE(STR)'
      Returns the string STR with the order of the characters reversed.
 
           mysql> SELECT REVERSE('abc');
                   -> 'cba'
 
      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.
 
 `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 multi-byte safe.
 
 `RTRIM(STR)'
      Returns the string STR with trailing space characters removed.
 
           mysql> SELECT RTRIM('barbar   ');
                   -> 'barbar'
 
      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 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 non-alphabetic
      characters are ignored in the given string. All international
      alphabetic characters outside the A-Z range are treated as vowels.
 
           mysql> SELECT SOUNDEX('Hello');
                   -> 'H400'
           mysql> SELECT SOUNDEX('Quadratically');
                   -> 'Q36324'
 
      * 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
      then duplicates, whereas the enhanced version discards duplicates
      first and then vowels.
 
 `EXPR1 SOUNDS LIKE EXPR2'
      This is the same as `SOUNDEX(EXPR1) = SOUNDEX(EXPR2)'. It is
      available only in MySQL 4.1 or later.
 
 `SPACE(N)'
      Returns a string consisting of N space characters.
 
           mysql> SELECT SPACE(6);
                   -> '      '
 
 `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.
 
           mysql> SELECT SUBSTRING('Quadratically',5);
                   -> 'ratically'
           mysql> SELECT SUBSTRING('foobarbar' FROM 4);
                   -> 'barbar'
           mysql> SELECT SUBSTRING('Quadratically',5,6);
                   -> 'ratica'
 
      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.
 
 `TRIM([{BOTH | LEADING | TRAILING} [REMSTR] FROM] STR)'
 `TRIM(REMSTR FROM] STR)'
      Returns the string STR with all REMSTR prefixes and/or suffixes
      removed. If none of the specifiers `BOTH', `LEADING', or
      `TRAILING' is given, `BOTH' is assumed. If REMSTR is optional and
      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.
 
 `UCASE(STR)'
      `UCASE()' is a synonym for `UPPER()'.
 
 `UNCOMPRESS(STRING_TO_UNCOMPRESS)'
      Uncompresses a string compressed by the `COMPRESS()' function.  If
      the argument is not a compressed value, the result is `NULL'.
      This function requires MySQL to have been compiled with a
      compression library such as `zlib'. Otherwise, the return value is
      always `NULL'.
 
           mysql> SELECT UNCOMPRESS(COMPRESS('any string'));
                   -> 'any string'
           mysql> SELECT UNCOMPRESS('any string');
                   -> NULL
 
      `UNCOMPRESS()' was added in MySQL 4.1.1.
 
 `UNCOMPRESSED_LENGTH(COMPRESSED_STRING)'
      Returns the length of a compressed string before compression.
 
           mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
                   -> 30
 
      `UNCOMPRESSED_LENGTH()' was added in MySQL 4.1.1.
 
 `UNHEX(STR)'
      Does the opposite of `HEX(STR)'. That is, it interprets each pair
      of hexadecimal digits in the argument as a number and converts it
      to the character represented by the number. The resulting
      characters are returned as a binary string.
 
           mysql> SELECT UNHEX('4D7953514C');
                   -> 'MySQL'
           mysql> SELECT 0x4D7953514C;
                   -> 'MySQL'
           mysql> SELECT UNHEX(HEX('string'));
                   -> 'string'
           mysql> SELECT HEX(UNHEX('1267'));
                   -> '1267'
 
      `UNHEX()' was added in MySQL 4.1.2.
 
 `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 UPPER('Hej');
                   -> 'HEJ'
 
      This function is multi-byte safe.
 
 

Menu

 
* String comparison functions  String Comparison Functions
 
Info Catalog (mysql.info.gz) Control flow functions (mysql.info.gz) Functions (mysql.info.gz) Numeric Functions
automatically generated byinfo2html