JavaScript Editor Source code editor     What Is Ajax 

↑

Main Page

12.4. String Functions

NameDescription
ASCII()Return numeric value of left-most character
BIN()Return a string representation of the argument
BIT_LENGTH()Return length of argument in bits
CHAR_LENGTH()Return number of characters in argument
CHAR()Return the character for each integer passed
CHARACTER_LENGTH()A synonym for CHAR_LENGTH()
CONCAT_WS()Return concatenate with separator
CONCAT()Return concatenated string
CONV()Convert numbers between different number bases
ELT()Return string at index number
<=>NULL-safe equal to operator
=Equal operator
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
>=Greater than or equal operator
>Greater than operator
HEX()Return a string representation of a hex 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
IS NULLNULL value test
ISTest a value against a boolean
LCASE()Synonym for LOWER()
LEFT()Return the leftmost number of characters as specified
LENGTH()Return the length of a string in bytes
<=Less than or equal operator
<Less than operator
LIKESimple 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
MID()Return a substring starting from the specified position
!=, <>Not equal operator
NOT LIKENegation of simple pattern matching
NOT REGEXPNegation of REGEXP
OCT()Return a string representation of the octal argument
OCTET_LENGTH()A synonym for LENGTH()
ORD()If the leftmost character of the argument is a multi-byte character, returns the code for that character
POSITION()A synonym for LOCATE()
QUOTE()Escape the argument for use in an SQL statement
REGEXPPattern matching using regular expressions
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
RLIKESynonym for REGEXP
RPAD()Append string the specified number of times
RTRIM()Remove trailing spaces
SOUNDEX()Return a soundex string
SOUNDS LIKE(v4.1.0)Compare sounds
SPACE()Return a string of the specified number of spaces
STRCMP()Compare two strings
SUBSTRING_INDEX()Return a substring from a string before the specified number of occurrences of the delimiter
SUBSTRING(), SUBSTR()Return the substring as specified
TRIM()Remove leading and trailing spaces
UCASE()Synonym for UPPER()
UNHEX()(v4.1.2)Convert each pair of hexadecimal digits to a character
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 7.5.2, “Tuning Server Parameters”.

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

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

12.4.1. String Comparison Functions

NameDescription
<=>NULL-safe equal to operator
=Equal operator
>=Greater than or equal operator
>Greater than operator
IS NULLNULL value test
ISTest a value against a boolean
<=Less than or equal operator
<Less than operator
LIKESimple pattern matching
!=, <>Not equal operator
NOT LIKENegation of simple pattern matching
SOUNDS LIKE(v4.1.0)Compare sounds

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 affects only comparisons.

Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.

  • expr LIKE pat [ESCAPE 'escape_char']

    Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

    The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

    Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

    mysql> SELECT 'д' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'д' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
mysql> SELECT 'д' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'д' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

    With LIKE you can use the following two wildcard characters in the pattern:

    CharacterDescription
    %Matches any number of characters, even zero characters
    _Matches exactly one character
    mysql> SELECT 'David!' LIKE 'David_';
        -> 1
mysql> SELECT 'David!' LIKE '%D%v%';
        -> 1

    To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, ‘\’ is assumed.

    StringDescription
    \%Matches one ‘%’ character
    \_Matches one ‘_’ character
    mysql> SELECT 'David!' LIKE 'David\_';
        -> 0
mysql> SELECT 'David_' LIKE 'David\_';
        -> 1

    To specify a different escape character, use the ESCAPE clause:

    mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1

    The escape sequence should be empty or one character long. As of MySQL 5.0.16, if the NO_BACKSLASH_ESCAPES SQL mode is enabled, the sequence cannot be empty.

    The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:

    mysql> SELECT 'abc' LIKE 'ABC';
        -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0

    In MySQL, LIKE is allowed on numeric expressions. (This is an extension to the standard SQL LIKE.)

    mysql> SELECT 10 LIKE '1%';
        -> 1

    Note: Because MySQL uses C escape syntax in strings (for example, ‘\n’ to represent a newline character), you must double any ‘\’ that you use in LIKE strings. For example, to search for ‘\n’, specify it as ‘\\n’. To search for ‘\’, specify it as ‘\\\\’; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against. (Exception: At the end of the pattern string, backslash can be specified as ‘\\’. At the end of the string, backslash stands for itself because there is nothing following to escape.)

  • expr NOT LIKE pat [ESCAPE 'escape_char']

    This is the same as NOT (expr LIKE pat [ESCAPE 'escape_char']).

    Note

    Aggregate queries involving NOT LIKE comparisons with columns containing NULL may yield unexpected results. For example, consider the following table and data:

    CREATE TABLE foo (bar VARCHAR(10));

INSERT INTO foo VALUES (NULL), (NULL);

    The query SELECT COUNT(*) FROM foo WHERE bar LIKE '%baz%'; returns 0. You might assume that SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%'; would return 2. However, this is not the case: The second query returns 0. This is because NULL NOT LIKE expr always returns NULL, regardless of the value of expr. The same is true for aggregate queries involving NULL and comparisons using NOT RLIKE or NOT REGEXP. In such cases, you must test explicitly for NOT NULL using OR (and not AND), as shown here:

    SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%' OR bar IS NULL;

  • expr NOT REGEXP pat, expr NOT RLIKE pat

    This is the same as NOT (expr REGEXP pat).

  • expr REGEXP pat, expr RLIKE pat

    Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression. The syntax for regular expressions is discussed in Section 12.4.2, “Regular Expressions”. Returns 1 if expr matches pat; otherwise it returns 0. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP, provided for mSQL compatibility.

    The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

    Note: Because MySQL uses the C escape syntax in strings (for example, ‘\n’ to represent the newline character), you must double any ‘\’ that you use in your REGEXP strings.

    REGEXP is not case sensitive, except when used with binary strings.

    mysql> SELECT 'Monty!' REGEXP 'm%y%%';
        -> 0
mysql> SELECT 'Monty!' REGEXP '.*';
        -> 1
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
        -> 1
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
        -> 1  0
mysql> SELECT 'a' REGEXP '^[a-d]';
        -> 1

    REGEXP and RLIKE use the current character set when deciding the type of a character. The default is latin1 (cp1252 West European). Warning: These operators are not multi-byte safe.

  • STRCMP(expr1,expr2)

    STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.

    mysql> SELECT STRCMP('text', 'text2');
        -> -1
mysql> SELECT STRCMP('text2', 'text');
        -> 1
mysql> SELECT STRCMP('text', 'text');
        -> 0

    STRCMP() uses the current character set when performing comparisons. This makes the default comparison behavior case insensitive unless one or both of the operands are binary strings.

12.4.2. Regular Expressions

NameDescription
NOT REGEXPNegation of REGEXP
REGEXPPattern matching using regular expressions
RLIKESynonym for REGEXP

A regular expression is a powerful way of specifying a pattern for a complex search.

MySQL uses Henry Spencer's implementation of regular expressions, which is aimed at conformance with POSIX 1003.2. See Appendix G, Credits. MySQL uses the extended version to support pattern-matching operations performed with the REGEXP operator in SQL statements. See Section 3.3.4.7, “Pattern Matching”, and Section 12.4.1, “String Comparison Functions”.

This section is a summary, with examples, of the special characters and constructs that can be used in MySQL for REGEXP operations. It does not contain all the details that can be found in Henry Spencer's regex(7) manual page. That manual page is included in MySQL source distributions, in the regex.7 file under the regex directory.

A regular expression describes a set of strings. The simplest regular expression is one that has no special characters in it. For example, the regular expression hello matches hello and nothing else.

Non-trivial regular expressions use certain special constructs so that they can match more than one string. For example, the regular expression hello|word matches either the string hello or the string word.

As a more complex example, the regular expression B[an]*s matches any of the strings Bananas, Baaaaas, Bs, and any other string starting with a B, ending with an s, and containing any number of a or n characters in between.

A regular expression for the REGEXP operator may use any of the following special characters and constructs:

  • ^

    Match the beginning of a string.

    mysql> SELECT 'fo\nfo' REGEXP '^fo$';                   -> 0
mysql> SELECT 'fofo' REGEXP '^fo';                      -> 1

  • $

    Match the end of a string.

    mysql> SELECT 'fo\no' REGEXP '^fo\no$';                 -> 1
mysql> SELECT 'fo\no' REGEXP '^fo$';                    -> 0

  • .

    Match any character (including carriage return and newline).

    mysql> SELECT 'fofo' REGEXP '^f.*$';                    -> 1
mysql> SELECT 'fo\r\nfo' REGEXP '^f.*$';                -> 1

  • a*

    Match any sequence of zero or more a characters.

    mysql> SELECT 'Ban' REGEXP '^Ba*n';                     -> 1
mysql> SELECT 'Baaan' REGEXP '^Ba*n';                   -> 1
mysql> SELECT 'Bn' REGEXP '^Ba*n';                      -> 1

  • a+

    Match any sequence of one or more a characters.

    mysql> SELECT 'Ban' REGEXP '^Ba+n';                     -> 1
mysql> SELECT 'Bn' REGEXP '^Ba+n';                      -> 0

  • a?

    Match either zero or one a character.

    mysql> SELECT 'Bn' REGEXP '^Ba?n';                      -> 1
mysql> SELECT 'Ban' REGEXP '^Ba?n';                     -> 1
mysql> SELECT 'Baan' REGEXP '^Ba?n';                    -> 0

  • de|abc

    Match either of the sequences de or abc.

    mysql> SELECT 'pi' REGEXP 'pi|apa';                     -> 1
mysql> SELECT 'axe' REGEXP 'pi|apa';                    -> 0
mysql> SELECT 'apa' REGEXP 'pi|apa';                    -> 1
mysql> SELECT 'apa' REGEXP '^(pi|apa)$';                -> 1
mysql> SELECT 'pi' REGEXP '^(pi|apa)$';                 -> 1
mysql> SELECT 'pix' REGEXP '^(pi|apa)$';                -> 0

  • (abc)*

    Match zero or more instances of the sequence abc.

    mysql> SELECT 'pi' REGEXP '^(pi)*$';                    -> 1
mysql> SELECT 'pip' REGEXP '^(pi)*$';                   -> 0
mysql> SELECT 'pipi' REGEXP '^(pi)*$';                  -> 1

  • {1}, {2,3}

    {n} or {m,n} notation provides a more general way of writing regular expressions that match many occurrences of the previous atom (or “piece”) of the pattern. m and n are integers.

    • a*

      Can be written as a{0,}.

    • a+

      Can be written as a{1,}.

    • a?

      Can be written as a{0,1}.

    To be more precise, a{n} matches exactly n instances of a. a{n,} matches n or more instances of a. a{m,n} matches m through n instances of a, inclusive.

    m and n must be in the range from 0 to RE_DUP_MAX (default 255), inclusive. If both m and n are given, m must be less than or equal to n.

    mysql> SELECT 'abcde' REGEXP 'a[bcd]{2}e';              -> 0
mysql> SELECT 'abcde' REGEXP 'a[bcd]{3}e';              -> 1
mysql> SELECT 'abcde' REGEXP 'a[bcd]{1,10}e';           -> 1

  • [a-dX], [^a-dX]

    Matches any character that is (or is not, if ^ is used) either a, b, c, d or X. A - character between two other characters forms a range that matches all characters from the first character to the second. For example, [0-9] matches any decimal digit. To include a literal ] character, it must immediately follow the opening bracket [. To include a literal - character, it must be written first or last. Any character that does not have a defined special meaning inside a [] pair matches only itself.

    mysql> SELECT 'aXbc' REGEXP '[a-dXYZ]';                 -> 1
mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]$';               -> 0
mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]+$';              -> 1
mysql> SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$';             -> 0
mysql> SELECT 'gheis' REGEXP '^[^a-dXYZ]+$';            -> 1
mysql> SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$';           -> 0

  • [.characters.]

    Within a bracket expression (written using [ and ]), matches the sequence of characters of that collating element. characters is either a single character or a character name like newline. The following table lists the allowable character names.

    The following table shows the allowable character names and the characters that they match. For characters given as numeric values, the values are represented in octal.

    NameCharacterNameCharacter
    NUL0SOH001
    STX002ETX003
    EOT004ENQ005
    ACK006BEL007
    alert007BS010
    backspace'\b'HT011
    tab'\t'LF012
    newline'\n'VT013
    vertical-tab'\v'FF014
    form-feed'\f'CR015
    carriage-return'\r'SO016
    SI017DLE020
    DC1021DC2022
    DC3023DC4024
    NAK025SYN026
    ETB027CAN030
    EM031SUB032
    ESC033IS4034
    FS034IS3035
    GS035IS2036
    RS036IS1037
    US037space' '
    exclamation-mark'!'quotation-mark'"'
    number-sign'#'dollar-sign'$'
    percent-sign'%'ampersand'&'
    apostrophe'\''left-parenthesis'('
    right-parenthesis')'asterisk'*'
    plus-sign'+'comma','
    hyphen'-'hyphen-minus'-'
    period'.'full-stop'.'
    slash'/'solidus'/'
    zero'0'one'1'
    two'2'three'3'
    four'4'five'5'
    six'6'seven'7'
    eight'8'nine'9'
    colon':'semicolon';'
    less-than-sign'<'equals-sign'='
    greater-than-sign'>'question-mark'?'
    commercial-at'@'left-square-bracket'['
    backslash'\\'reverse-solidus'\\'
    right-square-bracket']'circumflex'^'
    circumflex-accent'^'underscore'_'
    low-line'_'grave-accent'`'
    left-brace'{'left-curly-bracket'{'
    vertical-line'|'right-brace'}'
    right-curly-bracket'}'tilde'~'
    DEL177  
    mysql> SELECT '~' REGEXP '[[.~.]]';                     -> 1
mysql> SELECT '~' REGEXP '[[.tilde.]]';                 -> 1

  • [=character_class=]

    Within a bracket expression (written using [ and ]), [=character_class=] represents an equivalence class. It matches all characters with the same collation value, including itself. For example, if o and (+) are the members of an equivalence class, then [[=o=]], [[=(+)=]], and [o(+)] are all synonymous. An equivalence class may not be used as an endpoint of a range.

  • [:character_class:]

    Within a bracket expression (written using [ and ]), [:character_class:] represents a character class that matches all characters belonging to that class. The following table lists the standard class names. These names stand for the character classes defined in the ctype(3) manual page. A particular locale may provide other class names. A character class may not be used as an endpoint of a range.

    alnumAlphanumeric characters
    alphaAlphabetic characters
    blankWhitespace characters
    cntrlControl characters
    digitDigit characters
    graphGraphic characters
    lowerLowercase alphabetic characters
    printGraphic or space characters
    punctPunctuation characters
    spaceSpace, tab, newline, and carriage return
    upperUppercase alphabetic characters
    xdigitHexadecimal digit characters
    mysql> SELECT 'justalnums' REGEXP '[[:alnum:]]+';       -> 1
mysql> SELECT '!!' REGEXP '[[:alnum:]]+';               -> 0

  • [[:<:]], [[:>:]]

    These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

    mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';   -> 1
mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]';  -> 0

To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other. For example, to match the string 1+2 that contains the special + character, only the last of the following regular expressions is the correct one:

mysql> SELECT '1+2' REGEXP '1+2';                       -> 0
mysql> SELECT '1+2' REGEXP '1\+2';                      -> 0
mysql> SELECT '1+2' REGEXP '1\\+2';                     -> 1

JavaScript Editor Source code editor     What Is Ajax