Skip navigation links

Additional languages
11.4.1 String Comparison Functions »
Section Navigation      [Toggle]

11.4. String Functions

[+/-]

11.4.1. String Comparison Functions
11.4.2. Regular Expressions
Name Description
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
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
OCTET_LENGTH() A synonym for LENGTH()
ORD() Return character code for leftmost character of the argument
POSITION() A synonym for LOCATE()
QUOTE() Escape the argument for use in an SQL statement
REGEXP Pattern 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
RLIKE Synonym 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
SUBSTR() Return the substring as specified
SUBSTRING_INDEX() Return a substring from a string before the specified number of occurrences of the delimiter
SUBSTRING() 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
WEIGHT_STRING()(v5.2.4) Return the weight string for a string

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.

  • 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(0x65)), CHARSET(CHAR(0x65 USING utf8));
+---------------------+--------------------------------+
| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 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 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().

  • CONCAT(str1,str2,...)

    Returns the string that results from concatenating the arguments. 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; if you want to avoid that, you can use an explicit type cast, as in this example: 

    SELECT CONCAT(CAST(int_col AS CHAR), char_col);

    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'

  • 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,...)

    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 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 (defaults to 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('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 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)

    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. 

    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'

  • 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).

    If N_or_S is a string, returns a hexadecimal string representation of N_or_S where each character in N_or_S is converted to two hexadecimal digits. The inverse of this operation is performed by the UNHEX() function. 

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

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

    This function is multi-byte 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'

  • 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. To use this function, the file must be located on the server host, you must specify the full pathname 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 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 filenames 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. 

    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, 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 non-binary 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 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).

  • 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 × 2562) ...

    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 enclosed 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 enclosing single quotes. 

    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 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 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, or NULL if any argument is NULL. 

    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 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 multi-byte character sets, including utf-8.

      We hope to remove these limitations in a future release. 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 multi-byte 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 multi-byte 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 multi-byte safe.

  • UCASE(str)

    UCASE() is a synonym for UPPER().

  • UNHEX(str)

    Performs the inverse operation 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'

    The characters in the argument string must be legal hexadecimal digits: '0' .. '9', 'A' .. 'F', 'a' .. 'f'. If UNHEX() encounters any non-hexadecimal digits in the argument, it returns 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 'aa' is stored into a CHAR(3) column as 'aa ' and retrieved as 'aa' (with the trailing pad space stripped), so UNHEX() for the column value returns 'A'. By contrast 'aa' is stored into a BINARY(3) column as 'aa\0' and retrieved as 'aa\0' (with the trailing pad 0x00 byte not stripped). '\0' is not a legal hexadecimal digit, so UNHEX() for the column value returns NULL.

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

    UPPER() is ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). The description of LOWER() shows how to perform lettercase conversion of binary strings.

    This function is multi-byte safe.

  • WEIGHT_STRING(str [AS {CHAR|BINARY}(N)] [LEVEL levels] [flags])

    levels: N [ASC|DESC|REVERSE] [, N [ASC|DESC|REVERSE]] ...

    This function returns the weight string for the input string. The return value is a binary string that represents the sorting and comparison value of the string. It has these properties:

    WEIGHT_STRING() can be used for testing and debugging of collations, especially if you are adding a new collation. See Section 9.5, “How to Add a New Collation to a Character Set”.

    The input string, str, is a string expression. If the input is a non-binary (character) string such as a CHAR, VARCHAR, or TEXT value, the return value contains the collation weights for the string. If the input is a binary (byte) string such as a BINARY, VARBINARY, or BLOB value, the return value is the same as the input (the weight for each byte in a binary string is the byte value). If the input is NULL, WEIGHT_STRING() returns NULL.

    Examples: 

    mysql> SET @s = _latin1 'AB' COLLATE latin1_swedish_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 4142                   | 
+------+---------+------------------------+

    mysql> SET @s = _latin1 'ab' COLLATE latin1_swedish_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 4142                   | 
+------+---------+------------------------+

    mysql> SET @s = CAST('AB' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 4142                   | 
+------+---------+------------------------+

    mysql> SET @s = CAST('ab' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 6162                   | 
+------+---------+------------------------+

    The preceding examples use HEX() to display the WEIGHT_STRING() result. Because the result is a binary value, HEX() can be especially useful when the result contains non-printing values, to display it in printable form: 

    mysql> SET @s = CONVERT(0xC39F USING utf8) COLLATE utf8_czech_ci;
mysql> SELECT HEX(WEIGHT_STRING(@s));
+------------------------+
| HEX(WEIGHT_STRING(@s)) |
+------------------------+
| 0FEA0FEA               | 
+------------------------+

    For non-NULL return values, the data type of the value is VARBINARY if its length is within the maximum length for VARBINARY, otherwise the data type is BLOB.

    The AS clause may be given to cast the input string to a non-binary or binary string and to force it to a given length:

    • AS CHAR(N) casts the string to a non-binary string and pads it on the right with spaces to a length of N characters. N must be at least 1. If N is less than the length of the input string, the string is truncated to N characters. No warning occurs for truncation.

    • AS BINARY(N) is similar but casts the string to a binary string, N is measured in bytes (not characters), and padding uses 0x00 bytes (not spaces). 

    mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 41422020                            | 
+-------------------------------------+

    mysql> SELECT HEX(WEIGHT_STRING('ab' AS BINARY(4)));
+---------------------------------------+
| HEX(WEIGHT_STRING('ab' AS BINARY(4))) |
+---------------------------------------+
| 61620000                              | 
+---------------------------------------+

    The LEVEL clause may be given to specify that the return value should contain weights for specific collation levels.

    The levels specifier following the LEVEL keyword may be given either as a list of one or more integers separated by commas, or as a range of two integers separated by a dash. Whitespace around the punctuation characters does not matter.

    Examples: 

    LEVEL 1
LEVEL 2, 3, 5
LEVEL 1-3

    Any level less than 1 is treated as 1. Any level greater than the maximum for the input string collation is treated as maximum for the collation. The maximum varies per collation, but is never greater than 6.

    In a list of levels, levels must be given in increasing order. In a range of levels, if the second number is less than the first, it is treated as the first number (for example, 4-2 is the same as 4-4).

    If the LEVEL clause is omitted, MySQL assumes LEVEL 1 - max, where max is the maximum level for the collation.

    If LEVEL is specified using list syntax (not range syntax), any level number can be followed by these modifiers:

    • ASC: Return the weights without modification. This is the default.

    • DESC: Return bitwise-inverted weights (for example, 0x78f0 DESC = 0x870f).

    • REVERSE: Return the weights in reverse order (that is,the weights for the reversed string, with the first character last and the last first).

    Examples: 

    mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1));
+--------------------------------------+
| HEX(WEIGHT_STRING(0x007fff LEVEL 1)) |
+--------------------------------------+
| 007FFF                               | 
+--------------------------------------+

    mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC));
+-------------------------------------------+
| HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC)) |
+-------------------------------------------+
| FF8000                                    | 
+-------------------------------------------+

    mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1 REVERSE));
+----------------------------------------------+
| HEX(WEIGHT_STRING(0x007fff LEVEL 1 REVERSE)) |
+----------------------------------------------+
| FF7F00                                       | 
+----------------------------------------------+

    mysql> SELECT HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC REVERSE));
+---------------------------------------------------+
| HEX(WEIGHT_STRING(0x007fff LEVEL 1 DESC REVERSE)) |
+---------------------------------------------------+
| 0080FF                                            | 
+---------------------------------------------------+

    The flags clause currently is unused.


User Comments

Posted by Noam Rathaus on March 15 2006 9:31am[Delete] [Edit]

The following ORDER BY will sort a column called Host (varchar 255) that contains either a Hostname or IP addresses, whenever the IP address is found it will be sorted not as string but as integers:

ORDER BY
CAST(SUBSTRING(
Host,
1,
LOCATE('.', Host) - 1)
AS UNSIGNED),
CAST(SUBSTRING(
Host,
LOCATE('.', Host) + 1,
LOCATE('.', Host, LOCATE('.', Host) + 1)
- LOCATE('.', Host) - 1)
AS UNSIGNED),
CAST(SUBSTRING(
Host,
LOCATE('.', Host, LOCATE('.', Host) + 1) + 1,
LOCATE('.', Host,
LOCATE('.', Host, LOCATE('.', Host) + 1) + 1)
- LOCATE('.', Host, LOCATE('.', Host) + 1) - 1)
AS UNSIGNED),
CAST(SUBSTRING(
Host,
LOCATE('.', Host, LOCATE('.', Host,
LOCATE('.', Host) + 1) + 1) + 1,
3)
AS UNSIGNED)

Posted by Bob Collins on March 17 2006 8:56pm[Delete] [Edit]

MySQL does not include a function to split a delimited string. Although separated data would normally be split into separate fields within a relation data, spliting such can be useful either during initial data load/validation or where such data is held in a text field.

The following formula can be used to extract the Nth item in a delimited list, in this case the 3rd item "ccccc" in the example comma separated list.

select replace(substring(substring_index('aaa,bbbb,ccccc', ',', 3), length(substring_index('aaa,bbbb,ccccc', ',', 3 - 1)) + 1), ',', '') ITEM3

The above formula does not need the first item to be handled as a special case and returns empty strings correctly when the item count is less than the position requested.

More on this and related matters can be found at http://www.kanolife.com/escape/2006/03/mysql-string-splitter.html

Posted by [name withheld] on March 22 2006 8:02pm[Delete] [Edit]

This will split an IP address ("a.b.c.d") into 4 respective octets:

SELECT
`ip` ,
SUBSTRING_INDEX( `ip` , '.', 1 ) AS a,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', 2 ),'.',-1) AS b,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', -2 ),'.',1) AS c,
SUBSTRING_INDEX( `ip` , '.', -1 ) AS d
FROM log_table

Posted by [name withheld] on March 31 2006 4:37am[Delete] [Edit]

I found myself wanting a unique list of domain names from a table/column of fully qualified email addresses. There isn't a split function so using the other comments here I've devised this:

SELECT DISTINCT REVERSE(LEFT(REVERSE(email),LOCATE('@',REVERSE(email)) - 1)) AS domain FROM table ORDER BY domain

Reverses email, counts the characters from left minus the @. Reverses the reverse and returns 'domain.com'.

Perhaps there is a better/fast/easier way, however it's not easily found. So here is mine.

Posted by Dan Nelson on April 6 2006 7:23am[Delete] [Edit]

[name withheld], you could extract the domain in two simpler ways:

SELECT SUBSTRING_INDEX(email,'@',-1) AS domain FROM TABLE -- returns everything to the right of the rightmost @

SELECT SUBSTRING(email,INSTR(email,'@')+1) AS domain FROM TABLE -- returns everything to the right of the leftmost @

Both will return identical results on email addresses, since they only have one @ in them. I can't believe you didn't think of SUBSTRING_INDEX, even after the previous two comments used it :)

Posted by Tom O'Malley on April 17 2006 11:16pm[Delete] [Edit]

An example of how to make the first letter in a string uppercase - analogous to UCFIRST

SELECT CONCAT(UPPER(SUBSTRING(firstName, 1, 1)), LOWER(SUBSTRING(firstName FROM 2))) AS properFirstName

Posted by Martin Krsek on May 16 2006 12:33pm[Delete] [Edit]

CONCAT_WS(' AND ', NULL, NULL)
returns empty string instead of NULL, so

SET @where_cond = CONCAT_WS(' AND ', @where1, @where2);
SET @sql = CONCAT_WS(' WHERE ', 'SELECT * FROM table', @where_cond);
SELECT @sql;
results in
SELECT * FROM table WHERE
if both @where1 and @where2 are NULL

Posted by [name withheld] on May 20 2006 4:37am[Delete] [Edit]

For folks trying to lookup Countries associated with IPs (as in, e.g., databases found here: http://ip-to-country.webhosting.info/node/view/6), this should do the trick, building on an earlier contribution to this page (Assumes your IP is called "RemoteAddress"):

select RemoteAddress as IP,
( SUBSTRING_INDEX( RemoteAddress, '.', 1 ) * 16777216 +
SUBSTRING_INDEX(SUBSTRING_INDEX( RemoteAddress, '.', 2 ),'.',-1) * 65536 +
SUBSTRING_INDEX(SUBSTRING_INDEX( RemoteAddress, '.', -2 ),'.',1) * 256 +
SUBSTRING_INDEX( RemoteAddress, '.', -1 )
) AS IP2Num
FROM log
;


Posted by ali alikhan on May 30 2006 9:49am[Delete] [Edit]

A simple Split Procedure which could split a string containing record more then 22000. This procedure store splited values in table. Is there a way we could have our own Array.?


CREATE PROCEDURE dorepeat (in strResponse LONGTEXT , in Splitter CHAR)

Begin

DECLARE ResponseLength INT;
DECLARE ResponseStart INT;
DECLARE Marker INT;
DECLARE Previous INT;

DROP Table Split_Values;

CREATE TABLE Split_Values
(
split varchar(4000)
);

SET ResponseLength = LENGTH(strResponse);
SET Marker = 0;
SET ResponseStart = LOCATE(Splitter, strResponse, ResponseStart);
SET Marker = ResponseStart;
SET Previous = 1;
SET ResponseStart = ResponseStart + 1;

WHILE ResponseStart < ResponseLength DO
IF((ResponseStart = LOCATE(Splitter, strResponse, ResponseStart))!=0) Then

Set Marker = ResponseStart;


-- Insert into Split_Values VALUES (SUBSTRING(strResponse,Previous,Marker-Previous));

Set Previous = ResponseStart+1;

END IF;

Set ResponseStart = ResponseStart + 1;

END WHILE;

-- Insert into Split_Values VALUES (SUBSTRING(strResponse,Previous,ResponseStart-Marker));

End;
//

Posted by Erel Segal on July 24 2006 9:12am[Delete] [Edit]

Here is a trick to create a simple horizontal graph:

SELECT ColName, EXPORT_SET(pow(2,round(ColName))-1,'+','-','',70) FROM TableName

This will create an area filled with "+", where the length of each "+" bar equals the number in column ColName in that row.

70 is an upper bound on the values in ColName; change it to match your actual data.

Posted by Erel Segal on July 24 2006 9:18am[Delete] [Edit]

Correction to the previous tip: in the current version, EXPORT_SET does not create a string with more than 64 chars, even if you explicitly ask for 70 chars.

Another problem is that for numbers N > 53, 2^N - 1 equals 2^N because of rounding errors, so you will not see a bar, only a single "+".

Posted by Andrew Hanna on August 24 2006 8:04pm[Delete] [Edit]

I created a user-defined function in MySQL 5.0+ similar to PHP's substr_count(), since I could not find an equivalent native function in MySQL. (If there is one please tell me!!!)

delimiter ||
DROP FUNCTION IF EXISTS substrCount||
CREATE FUNCTION substrCount(s VARCHAR(255), ss VARCHAR(255)) RETURNS TINYINT(3) UNSIGNED LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE count TINYINT(3) UNSIGNED;
DECLARE offset TINYINT(3) UNSIGNED;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;

SET count = 0;
SET offset = 1;

REPEAT
IF NOT ISNULL(s) AND offset > 0 THEN
SET offset = LOCATE(ss, s, offset);
IF offset > 0 THEN
SET count = count + 1;
SET offset = offset + 1;
END IF;
END IF;
UNTIL ISNULL(s) OR offset = 0 END REPEAT;

RETURN count;
END;

||
delimiter ;

Use like this:

SELECT substrCount('/this/is/a/path', '/') `count`;

`count` would return 4 in this case. Can be used in such cases where you might want to find the "depth" of a path, or for many other uses.

Posted by Michael Newton on August 31 2006 5:28pm[Delete] [Edit]

To [name withheld] who suggested a method for turning IP addresses into numbers, I would suggest that the INET_ATON() function is a little easier to use!

Posted by Chris Stubben on August 21 2008 3:49pm[Delete] [Edit]

It's pretty easy to create your own string functions for many examples listed here

## Count substrings

CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
return (length(x)-length(REPLACE(x, delim, '')))/length(delim);

SELECT substrCount('/this/is/a/path', '/') as count;

+-------+
| count |
+-------+
|     4 |
+-------+

SELECT substrCount('/this/is/a/path', 'is') as count;
+-------+
| count |
+-------+
|     2 |
+-------+

## Split delimited strings

CREATE FUNCTION strSplit(x varchar(255), delim varchar(12), pos int) returns varchar(255)
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');

select strSplit("aaa,b,cc,d", ',', 2) as second;
+--------+
| second |
+--------+
| b      |
+--------+

select strSplit("a|bb|ccc|dd", '|', 3) as third;
+-------+
| third |
+-------+
| ccc   |
+-------+

select strSplit("aaa,b,cc,d", ',', 7) as 7th;
+------+
| 7th  |
+------+
| NULL |
+------+

## Upper case first letter, UCFIRST or INITCAP

CREATE FUNCTION ucfirst(x varchar(255)) returns varchar(255)
return concat( upper(substring(x,1,1)),lower(substring(x,2)) );

select ucfirst("TEST");

+-----------------+
| ucfirst("TEST") |
+-----------------+
| Test            |
+-----------------+

##Or a more complicated example, this will repeat an insert after every nth position.

drop function insert2;
DELIMITER //
CREATE FUNCTION insert2(str text, pos int, delimit varchar(124))
RETURNS text
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE str_len INT;
DECLARE out_str text default '';
SET str_len=length(str);
WHILE(i<str_len) DO
SET out_str=CONCAT(out_str, SUBSTR(str, i,pos), delimit);
SET i=i+pos;
END WHILE;
-- trim delimiter from end of string
SET out_str=TRIM(trailing delimit from out_str);
RETURN(out_str);
END//
DELIMITER ;

select insert2("ATGCATACAGTTATTTGA", 3, " ") as seq2;
+-------------------------+
| seq2                    |
+-------------------------+
| ATG CAT ACA GTT ATT TGA |
+-------------------------+


Posted by w pociengel on October 20 2006 12:28pm[Delete] [Edit]

I was trying to output a text message that told me if a field was null. I tried various mechanisms but this proved to do the trick.

elt(((field1 <=> NULL) + 1),"not null", "null")

evaluating (field1 <=> NULL) returns 0 (zero) if the field is not null and 1 (one) if the field is null. Adding 1 (one) to this result provides positional information that fits what 'elt' expects.

elt will return "not null" (position 1) if the evaluation of ((field1 <=> NULL) + 1) = 1

it will return "null" (position 2) if the evaluation of ((field1 <=> NULL) + 1) = 2

This can be altered to output messages based on any test that I've tried. Just remember that 'elt' returns null or 1 for a comparison so you need to add 1 (one) to that result to be able to choose between different messages.

Posted by J Vera on October 26 2006 10:41pm[Delete] [Edit]

As above I couldn't find a function for splitting strings based on a character set rather than string position, where the results were independent of substring lengths. I used this query to split out the Swiss-Prot accession numbers from BLAST result subject ID's, which are bracketed by pipe ('|') characters, but any two relatively unique characters should work.

select left(substring(<columnName>,locate('|',<columnName>)+1),
locate('|',substring(<columnName>,
locate('|',<columnName>)+1))-1)
as '<resultColumnName>' from <table>

Posted by Giovanni Campagnoli on December 20 2006 11:58am[Delete] [Edit]

This is the php function strip_tags

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;
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;
return x;
END;
||
delimiter ;

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

+------------+
| strip_tags |
+------------+
| Hi, mate!  |
+------------+
1 row in set (0.00 sec)



Posted by Stephen Gornick on December 24 2006 5:17am[Delete] [Edit]

If using statement-based replication, load_file() will fail on the slave unless the same file exists on the slave as on the master.

Posted by Nicola De Franceschi on January 16 2007 10:02pm[Delete] [Edit]

Here's my formula to remove a value from a string field of comma separated values. You can use a different delimiter just repalce the comma in the formula but pay attention since lot of commas here are the argument separator of the used functions.
The nice part is that with this formula you don't need to distinguish the first and last element of the string and this formula removes just the value_to_remove so if your initial string is: "4,11,34" and the value you want to remove is "4" you'll get "11,34".

UPDATE temp SET string = TRIM(BOTH ',' FROM REPLACE(CONCAT("," , string, ","), CONCAT(",",'value_to_remove', ",") , ',')) WHERE id=1

Posted by Robert Glover on February 13 2007 3:04pm[Delete] [Edit]

There is a simple way to convert the following Oracle usage of decode into MySql:

Oracle version:

select BU, count(line_number) total,
sum(decode(RECERTIFY_FLAG,'Y',1,0)) needed,
sum(decode(RECERTIFY_FLAG,'N',1,0)) not_needed,
sum(decode(RECERTIFY_FLAG,'Y',0,'N',0,1)) not_processed
from isf.isf_analog_line group by bu order by bu

MySql version that gives same results:

select BU, count(line_number) total,
sum(FIND_IN_SET(RECERTIFY_FLAG,'Y')) needed,
sum(FIND_IN_SET(RECERTIFY_FLAG,'N')) not_needed,
sum(FIND_IN_SET(RECERTIFY_FLAG,' ')) not_processed
from isf.isf_analog_line group by bu order by bu

Add your own comment.

Posted by Balaji Devarajan on March 8 2007 1:37am[Delete] [Edit]

I was looking for word_count("string") in mysql, finally came up with an user defined function which is very usefull for me, note: I used <space> for actual space.
DROP FUNCTION IF EXISTS word_count;
CREATE FUNCTION word_count (f_string text(5000)) RETURNS smallint(10)
BEGIN
DECLARE new_string text(5000);
WHILE INSTR(f_string,'<space><space>')>0
DO
SET new_string=(select REPLACE(f_string,'<space><space>','<space>'));
SET f_string=new_string;
END WHILE;

RETURN (select LENGTH(TRIM(f_string))-LENGTH(REPLACE(TRIM(f_string),'<space>',''))+1);
END
//
Here is the result
mysql> select word_count("<space>Balaji<space><space><space> Devarajan<space>") WORD_COUNT;

+------------+
| WORD_COUNT |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)
mysql> select word_count(" Balaji Devarajan ") WORD_COUNT;
+------------+
| WORD_COUNT |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)
mysql> select word_count("Balaji Devarajan") WORD_COUNT;
+------------+
| WORD_COUNT |
+------------+
|          2 |
+------------+
1 row in set (0.01 sec)

Posted by Balaji Devarajan on March 8 2007 1:36am[Delete] [Edit]

Here is another function I wrote, which is very usefull in getting the domain name from the url, please bare with the spaces in the http : //, if not iam not able to submit this with many urls
delimiter //
drop function if exists sub_domain;
CREATE FUNCTION sub_domain (url text(1000)) RETURNS CHAR(50)
BEGIN
DECLARE str1 varchar(10);
DECLARE str2 varchar(10);
SET str1=http : //;
SET str2=https://;
RETURN if(substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-1) != 'com',substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-3),substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-2));
END;
//
mysql> select domain("http : //maps.google.com/maps?hl=en&q=what%20is%20domain%20name&btnG=Google+Search&ie=UTF-8&oe=UTF-8&um=1&sa=N&tab=il") DOMAIN;

+------------+
| DOMAIN     |
+------------+
| google.com | 
+------------+
1 row in set (0.00 sec)
similarly we can get the sub-domain too.
delimiter //
drop function if exists sub_domain;
CREATE FUNCTION sub_domain (url text(1000)) RETURNS CHAR(50)
BEGIN
DECLARE str1 varchar(10);
DECLARE str2 varchar(10);
SET str1=http : //;
SET str2=https : //;
RETURN if(substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-1) != 'com',substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-3),substring_index(substring_index(substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-3));
END;
//
mysql> select sub_domain("http : //maps.google.com/maps?hl=en&q=what%20is%20domain%20name&btnG=Google+Search&ie=UTF-8&oe=UTF-8&um=1&sa=N&tab=il") sub_domain;
+-----------------+
| sub_domain      |
+-----------------+
| maps.google.com | 
+-----------------+
1 row in set (0.00 sec)

Posted by Balaji Devarajan on March 24 2007 12:05pm[Delete] [Edit]

I was looking for function, to give me the MOST COMMON VALUE IN A STRING. Used Java or php to do this, using substrCount and SplitString (thanks to Chris Stubben). I got the below function, which will give me the mcv value.

DELIMITER //
DROP FUNCTION IF EXISTS get_mcv;
CREATE FUNCTION get_mcv (list text(10000)) RETURNS text(1000)
BEGIN
DECLARE cnt int(10);
DECLARE iter_cnt int(10);
DECLARE item text(100);
DECLARE f_item text(100);
DECLARE prv_cnt int(10) default 0;
DECLARE nxt_cnt int(10) default 0;

IF list=' ' THEN
RETURN list;
END IF;

select substrCount(list,',')+1 into cnt;
SET iter_cnt = 1;

while cnt >= iter_cnt
do
select charsplit(list,',',iter_cnt) into item;

select substrCount(concat(',',list,','),concat(',',item,',')) into nxt_cnt;
IF nxt_cnt > prv_cnt THEN
SET prv_cnt = nxt_cnt;
SET f_item = item;
END IF;

set iter_cnt=iter_cnt+1;
end while;
RETURN f_item;

END
//


mysql> select get_mcv("dsfds,dsfds,fdfds,dfdsf,sd,df,df,df,df");

+---------------------------------------------------+
| get_mcv("dsfds,dsfds,fdfds,dfdsf,sd,df,df,df,df") |
+---------------------------------------------------+
| df                                                | 
+---------------------------------------------------+
1 row in set (0.02 sec)

Posted by S D on March 28 2007 7:47am[Delete] [Edit]

A field may contain delimited values that may be used with 'IN' operator in a where clause. However each of the values need to be nested within single quote(').
This function uses stringSplit and substrCount - thanks to Chris Stubben.
CREATE FUNCTION cs2in(x varchar(255), delim varchar(12)) returns varchar(255) deterministic
BEGIN
DECLARE retstr varchar(255);
DECLARE Valcount INT(10);
DECLARE v1 INT(10);
SET retstr = '';
SET Valcount = substrCount(x,delim)+1;
SET v1=0;
WHILE (v1 < Valcount) DO
SET retstr = concat_ws(',',retstr,quote(stringSplit(x,delim,v1+1)));
SET v1 = v1 + 1;
END WHILE;
SET retstr = CONCAT('(',TRIM(LEADING ',' FROM TRIM(retstr)),')');
RETURN retstr;
END

E.g.
mysql> Select cs2in('1,2,3,4,5',',') as IN_format;

+---------------------+
| IN_format           |
+---------------------+
|('1','2','3','4','5')|
+---------------------+

This format is compatible for use in the 'IN' clause.

Posted by LEO DIVINAGRACIA on June 14 2007 11:57pm[Delete] [Edit]

for a DIFFERENCE like function when comparing two strings together, try this:

------------------------------

DELIMITER $$

DROP FUNCTION IF EXISTS `db2`.`diff3`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `diff3`(n1 varchar(50), n2 varchar(50)) RETURNS int(11)
DETERMINISTIC
BEGIN
declare s1 char(1);
declare s2 char(1);
declare s3 int;
declare s4 int;
declare l1 int;
declare diff int;
set l1 = 1;
set diff = 0;
repeat
set s1 = substring(soundex(n1),l1,1);
set s2 = substring(soundex(n2),l1,1);
if s1 = s2 then set diff = diff + 1;
end if;
set l1 = l1 + 1;

until l1 > 4
end repeat;
return diff;
END$$

DELIMITER ;

----------------------

other DBMS have this function and i kinda needed one. so looked and mysql's online docs shows a DIFFERENCE function but that was for GIS apps and isnt current implemented.

just change the "user@hostname" and the "db.function_name" to reflect your info.

returns an INT value from 0 to 4, where 0 means the SOUNDEX of each string doesnt have any same value. 4 means each 4 alphanumeric digit is the same:

select soundex("hello"), soundex("jello")

returns

H400 and J400

so DIFF3("hello", "jello")

returns a 3

while DIFF3("hello","great")

returns a 1

as a stored function, you can do something like:

select firstname from mytable where diff3("joe bob", firstname) > 3


Posted by Brett Millett on June 29 2007 3:06am[Delete] [Edit]

Just some syntax to pass along for those who may wanna reformat credit cards (if you are brave enough to store them as plain text in the first place) before sending them to an application for displaying. This lets you pad out all the numbers except the last four (all you developers have done this millions of times I'm sure.)

SELECT LPAD(SUBSTRING(`cardnumbercolumn`, -4, 4),LENGTH(`cardnumbercolumn`),'*') FROM table

Posted by Nigel Forward on July 6 2007 12:21pm[Delete] [Edit]

To format a number with leading zeros you can use LPAD:

eg: LPAD(13, 4, '0') returns '0013'

Any numbers that generate more than the number of digits (4 in this case) would be truncated from the left:

eg: LPAD(41278, 4, '0') returns '1278'

Posted by ben j on September 4 2007 3:23pm[Delete] [Edit]

Simple Split
SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( 'a|b|c|d|e|f|g|h', '|', index), '|', -1 );


Posted by Andrej Abehtikov on September 19 2007 9:12am[Delete] [Edit]

A simple way to convert IP address from string '0A0B0C0D' into 10.11.12.13 :
CONCAT(
ASCII(UNHEX(SUBSTRING(yourIP,1,2))), '.',
ASCII(UNHEX(SUBSTRING(yourIP,3,2))), '.',
ASCII(UNHEX(SUBSTRING(yourIP,5,2))), '.',
ASCII(UNHEX(SUBSTRING(yourIP,7,2)))
)

Posted by Steven Benjamin on November 12 2007 7:53pm[Delete] [Edit]

I sometimes need to find the last occurrence of a string. Using a previous post I did this by implementing the following procedure

delimiter ||
DROP FUNCTION IF EXISTS locatelast||
CREATE FUNCTION locatelast(s VARCHAR(1000), ss VARCHAR(1000)) RETURNS TINYINT(3) UNSIGNED LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE last TINYINT(3) UNSIGNED;
DECLARE offset TINYINT(3) UNSIGNED;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;

SET last = 0;
SET offset = 1;

REPEAT
IF NOT ISNULL(s) AND offset > 0 THEN
SET offset = LOCATE(ss, s, offset);
IF offset > 0 THEN
SET last = offset;
SET offset = offset + 1;
END IF;
END IF;
UNTIL ISNULL(s) OR offset = 0 END REPEAT;

RETURN last;
END;


Posted by Denis Roy on November 15 2007 7:46pm[Delete] [Edit]

In response to Steven Benjamin on November 12 2007 7:53pm

Instead of looping through the string to look for the last occurrence, simply reverse() the string and look for the first occurrence, then substract the found position from the string length:

selec