How to Use MySQL String Functions

Introduction

MySQL string functions allow users to manipulate data strings or query information about a string returned by the SELECT query.

In this article, you will learn how to use MySQL string functions.

Prerequisites

• MySQL Server and MySQL Shell installed
• A MySQL user account with root privileges

MySQL String Functions Cheat Sheet

Every string function is explained and exemplified in the article below. If it is more convenient for you, you can save the cheat sheet PDF by clicking the Download MySQL String Functions Cheat Sheet link.

Download MySQL String Functions Cheat Sheet

ASCII()

The syntax for the ASCII() function is:

ASCII('str')

The ASCII() string returns the ASCII (numeric) value of the leftmost character of the specified str string. The function returns 0 if no str is specified. Returns NULL if str is NULL.

Use ASCII() for characters with numeric values from 0 to 255.

For example:

In this example, the ASCII() function returns the numeric value of p, the leftmost character of the specified str string.

BIN()

The syntax for the BIN() function is:

BIN(number)

The BIN() function returns a binary value of the specified number argument, where the number is a BIGINTEGER number. Returns NULL if the number argument is NULL.

For example, the following query returns a binary representation of the number 25:

BIT_LENGTH()

The syntax for the BIT_LENGTH() function is:

BIT_LENGTH('str')

The function outputs the length of the specified str string in bits.

For example, the following query returns the bit length of the specified ‘example‘ string:

CHAR()

The syntax for the CHAR() function is:

CHAR(number,... [USING charset_name])

CHAR() interprets each specified number argument as an integer and outputs a binary string of characters from the ASCII table. The function skips NULL values.

For example:

If you want to produce an output other than binary, use the optional USING clause and specify the desired character set. MySQL issues a warning if the result string is illegal for the specified character set.

CHAR_LENGTH(), i.e., CHARACTER_LENGTH()

The syntax for the CHAR_LENGTH function is:

CHAR_LENGTH(str)

The function outputs the length of the specified str string, measured in characters.

CHAR_LENGTH() treats a multibyte character as a single character, which means that a string containing four 2-byte characters returns 4 as a result, whereas LENGTH() returns 8.

For example:

CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

CONCAT()

The CONCAT() function concatenates two or more specified strings. The syntax is:

CONCAT(string1,string2,...)

The CONCAT function converts all arguments to the string type before concatenating. If all arguments are nonbinary strings, the result is a non-binary string. On the other hand, concatenating binary strings results in a binary string. A numeric argument is converted to its equivalent nonbinary string form.

If any of the specified arguments are NULL, CONCAT() returns NULL as a result.

For example:

The function puts the specified strings together into one, in this case, ‘phoenixNAP‘.

CONCAT_WS()

The syntax for CONCAT_WS() is:

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() is a special form of CONCAT() that puts two or more expressions together and includes a separator. The separator splits the strings that you want to concatenate. If the separator is NULL, the result is NULL.

For example:

In this example, the separator is a blank space that separates the specified strings in the output.

ELT()

The syntax for the ELT() function is:

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

The N argument defines which of the specified strings to return as a result. ELT() returns NULL if N is less than 1 or greater than the number of specified strings.

For example:

EXPORT_SET()

The syntax for EXPORT_SET() is:

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

The EXPORT_SET() function returns an ON or OFF string for every bit of the first argument, checking from right to left. The argument is an integer, but the function converts it into bits.

If the bit is 1, the function returns the ON string. If the bit is 0, the function returns OFF. EXPORT_SET() places a separator between the return values. The default separator is a comma, but you can specify a different one as the fourth argument.

The strings are added to the output result from left to right, separated by the separator string. The number_of_bits argument specifies how many bits to examine.

For example:

Explanation:

1. After conversion, the first argument 5 stands for 00000101.

2. Checking from right to left, the first bit is 1, so the function returns the ‘Yes‘ argument (the ON string). The second bit is 0, so the function returns ‘No‘ (the OFF string). For the third bit, it returns ‘Yes.’ For all the remaining bits (zeros), it returns ‘No.’

3. The fourth argument ‘–‘ is specified as a separator in the return result.

FIELD()

The syntax for the FIELD() syntax is:

FIELD(str,str1,str2,str3,...)

The function returns the index position of a string in a string list. If there is no such string, the output is 0. If the string is NULL, the function returns 0. The FIELD() function is case insensitive.

For example:

The function returns 6, which is the position of the string ‘f‘ in the list.

FIND_IN_SET()

The syntax for the FIND_IN_SET() function is:

FIND_IN_SET(str,strlist)

The function returns the position of a string in a list of strings. If there are several string instances, the output returns only the first position of the specified string.

For example:

FORMAT()

The syntax for the FORMAT() function is:

FORMAT(X,D)

The function outputs the specified number X in a format like ‘#,###,###.##’, rounded to the specified number of decimal places D. The result has no decimal point if D is 0.

Users can also specify the locale after the D argument, which affects the output.

For example:

The output rounds the number to 3 decimal places, and the German locale causes a . symbol to denote thousands and the , character to denote fractions.

FROM_BASE64()

The syntax for the FROM_BASE64() function is:

FROM_BASE64(str)

The function decodes the specified base-64 encoded string and returns the result as a binary string. If the argument is NULL or an invalid base-64 string, the result is NULL.

FROM_BASE64() is the reverse of TO_BASE64() as TO_BASE64() encodes a query in base64.

For example:

The first query encodes the specified string in base64. The second query decodes the base64 encoded string and returns the original value.

HEX()

The syntax for the HEX() function is:

HEX(N_or_S)

The function returns a string representation of a hexadecimal value of the specified N decimal value or S string value.

If the argument is a string, HEX converts each character to two hexadecimal digits. On the other hand, if the argument is a decimal, the output is a hexadecimal string representation of the argument and treats it as a BIGINTEGER number.

The HEX() string function is equivalent to the mathematical function CONV(N,10,16).

For example:

The output returns the hexadecimal value of the specified string.

INSERT()

The syntax for the INSERT() function is:

INSERT(str,pos,len,newstr)

The function inserts a newstr string within the str string and removes the len number of original characters beginning at the pos position.

If the pos argument isn’t within the original string length, INSERT() returns the original string.

If the len argument isn’t within the length of the rest of the string, INSERT() replaces the rest of the string from the pos position.

If any argument is NULL, INSERT() returns NULL.

For example:

The output is the original string with the new string inserted at position 5, with no original characters removed.

INSTR()

The syntax for the INSTR() function is:

INSTR(str,substr)

The function outputs the position of the first appearance of the substr substring in the original str string.

The function works the same way as LOCATE(), except the argument order is reversed.

For example:

The output indicates the substring location – position 8.

LEFT()

The syntax for the LEFT() function is:

LEFT('str', chars)

The function outputs the number of leftmost characters chars from the specified str string.

If any argument is NULL, the output is also NULL.

For example:

LENGTH(), i.e., OCTET_LENGTH()

The syntax for the LENGTH() function is:

LENGTH(str)

The function outputs the str string length in bytes. Multibyte characters count as multiple bytes.

For example:

The OCTET_LENGTH() function is a synonym for LENGTH().

LIKE

The syntax for the LIKE function is:

expr LIKE pat

The function performs pattern matching by finding the specified string pattern within other strings.

LIKE supports wildcards:

• % -Matches any number of characters, even zero.
• _ – Matches exactly one character.

LIKE returns 1 (true) or 0 (false). If the expr expression or pat pattern is NULL, the output is also NULL.

For example:

In this example, we retrieved all the customers whose name begins with ‘A‘.

LOAD_FILE()

The syntax for the LOAD_FILE() function is:

LOAD_FILE(file_name)

The function reads the file and outputs a string containing the file contents. The prerequisites for this function are:

• Having the file on the server host.
• Specifying the full file path in place of the file_name argument.
• Having the FILE privilege.

The server must be able to read the file, and its size must be less than max_allowed_packet bytes. If the secure_file_priv system variable is a non-empty directory name, place the file in that directory.

If the file doesn’t exist or the function cannot read it for one of the above reasons, the output is NULL.

For example:

LOCATE(), i.e., POSITION()

The syntax for the LOCATE() function is:

LOCATE(substring,str,[position])

The function outputs the position of the first occurrence of the specified substring argument within the str string. The position argument is optional and used to specify from which str string position to start searching. Omitting the position argument starts searching from the beginning.

If the substring is not in the str string, LOCATE() returns 0. If any argument is NULL, the function returns NULL.

For example:

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

LOWER(), i.e., LCASE()

The syntax for the LOWER() function is:

LOWER(str)

The function changes all characters of the specified str string to lowercase and outputs the result. The default character set mapping it uses is utf8mb4. LOWER() is multibyte safe.

For example:

The LCASE() function is a synonym for LOWER().

LPAD()

The syntax for the LPAD() function is:

LPAD(str,len,padstr)

The function outputs the specified str string, left-padded with the padstr string, to a length of len characters. The function shortens the output to len characters if the str argument is longer than len.

LPAD() is multibyte safe.

For example:

In this example, the LPAD() function left-pads the specified argument with the specified padstr, up to 10 characters.

LTRIM()

The syntax for the LTRIM() function is:

LTRIM(str)

The function outputs the specified str string without the leading space characters.

For example:

MAKE_SET()

The syntax for the MAKE_SET() function is:

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

The function outputs a set value, i.e., a string containing the specified substrings with the corresponding bit specified in the bits argument.

The str1 argument corresponds to bit 0, str2 corresponds to bit 1, etc. If any of the arguments is NULL, they don’t appear in the result.

For example:

In this example, the first bit is 1, i.e., 001. The rightmost digit is 1, so the function returns ‘phoenix.’ The second bit is 2, i.e., 010, the middle number is 1, so the function returns ‘NAP,’ thus completing the output.

MATCH()

The syntax for the MATCH() function is:

MATCH(col1, col2,…) AGAINST(expr[search_modifier])

The function allows users to perform full-text searches by specifying a list of columns separated by commas. Enter a string you want to search for in place of the expr argument.

The search_modifier argument is optional and indicates the search type. The accepted values are:

• IN NATURAL LANGUAGE MODE (default)
• IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
• IN BOOLEAN MODE
• WITH QUERY EXPANSION

For example:

NOT LIKE

The syntax for the NOT LIKE function is:

expr NOT LIKE pat [ESCAPE 'escape_char']

NOT LIKE is a negation of LIKE, meaning that it operates under the same conditions as LIKE and uses the same wildcards.

For example:

The output lists all customers and their city except the customers whose name starts with ‘A.’

NOT REGEXP

The syntax for the NOT REGEXP function is:

expr NOT REGEXP pat

The function performs a pattern match of the expr string against the pat pattern. The pattern can be an extended regular expression.

NOT REGEXP is a negation of REGEXP.

If the expr argument matches the pat argument, the output is 1. Otherwise, the output is 0. If either argument is NULL, the output is NULL.

For example:

The above example outputs all customers who don’t live in cities starting with L. The ‘^‘ character marks the start of the city name.

OCT()

The syntax for the OCT() function is:

OCT(N)

The function outputs the octal value of the specified N argument, where N is a BIGINTEGER number. If N is NULL, the function returns NULL.

For example:

ORD()

The syntax for the ORD() function is:

ORD(str)

The function finds the code of the leftmost multibyte character in a string. If the leftmost character isn’t multibyte, ORD() returns the character’s ASCII value.

The function calculates the character code from the numeric values of its constituent bytes. The formula used for this operation is:

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

For example:

QUOTE()

The syntax for the QUOTE() function is:

QUOTE(str)

The function outputs a string that represents properly escaped data value usable in an SQL statement. Single quotes enclose the string and it contains a backslash (\) before each instance of backslash (\), single quote (‘), ASCII NUL, and Control+Z.

If the str argument is NULL, the output is NULL.

For example:

The example above selects all customers that live in the UK and encloses their addresses in single quotes.

REGEXP_LIKE(), REGEXP, RLIKE

The syntax for the REGEXP_LIKE() function is:

REGEXP_LIKE(expr, pat, [match_type])

The function outputs 1 if the expr string matches the expression specified in place of the pat argument. Otherwise, the output is 0. If the expr or pat argument is NULL, the output value is NULL.

The match_type argument is optional and represents a string that may contain any or all of the following flags that specify the matching type:

• Case-sensitive matching (c). Handle the arguments as binary strings with case sensitivity if either argument is a binary string. The c flag means case sensitivity is adopted even if the i flag is also specified.
• Case-insensitive matching (i). Handle the arguments without case sensitivity.
• Multiple-line mode (m). Recognize line terminators within the string. The default setting is to match line terminators only at the string expression start and end.
• The . character matches line terminators (n). Used to modify the . (dot) character to match line terminators. By default, . matching stops at the end of a line.
• Unix-only line endings (u). Unix-only line endings that recognize only the newline character by the ., ^, and $ match operators.

If contradictory flags are specified within match_type, the rightmost one takes precedence.

REGEXP and RLIKE are synonyms for REGEXP_LIKE().

For example:

In this example, the regular expression can specify any character in place of the dot, so the function outputs a 1 to indicate a match.

REGEXP_INSTR()

The syntax for the REGEXP_INSTR() function is:

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])

The function outputs the starting index of a substring that matches the expr expression’s pat pattern. If there is no match, the output is 0. If either argument is NULL, the output is NULL. Character indexes begin at 1.

The optional arguments are:

• pos – Specify the position in expr where to start the search. If omitted, the default is 1.
• occurrence – Specify which occurrence of a match to search for. If omitted, the default is 1.
• return_option – Which position type to return. If set to 0, REGEXP_INSTR() returns the matched substring’s first character position. If set to 1, REGEXP_INSTR() returns the position following the matched substring. If omitted, the default is 0.
• match_type – Specifies how to match. The argument is the same as in REGEXP_LIKE() and takes the same flags.

For example:

In this example, there is a match, and the substring starts at position 1.

REGEXP_REPLACE()

The syntax for the REGEXP_REPLACE() function is:

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

The function replaces every occurrence in the expr string specified by the pat pattern with the repl string, and outputs the resulting string. If there is a match, the output is the whole string with the replacements. If there is no match, the output is the original expr string. If any argument is NULL, the output is NULL.

The optional REGEXP_REPLACE() arguments are:

• pos – The position in expr where to start the search. If omitted, the default is 1.
• occurrence – Which match occurrence to replace. If omitted, the default is 0 and replaces all occurrences.
• match_type – Specifies how to match. The argument is the same as in REGEXP_LIKE() and takes the same flags.

For example:

REGEXP_SUBSTR()

The syntax for the REGEXP_SUBSTR() function is:

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])

The function outputs the substring of the expr string that matches the regular expression specified by the pat pattern. If there is no match, the result is NULL. If any argument is NULL, the output is NULL.

The optional arguments are:

• pos – The position in expr where to start the search. If omitted, the default is 1.
• occurrence – Which match occurrence to replace. If omitted, the default is 1.
• match_type – Specifies how to match. The argument is the same as in REGEXP_LIKE() and takes the same flags.

For example:

In this example, the result outputs the matching substring from the specified expr string.

REPEAT()

The syntax for the REPEAT() function is:

REPEAT(str,count)

The function outputs a string that repeats the str string count times. If the count argument is less than 1, the function outputs an empty string. If either argument is NULL, the result is NULL.

For example:

In the example above, the function outputs a string consisting of the ‘Work‘ string repeated six times.

REPLACE()

The syntax for the REPLACE() function is:

REPLACE(str,from_str,to_str)

The function replaces all instances of from_str within the str string with the specified to_str string. The function is case-sensitive and multibyte safe.

For example:

REVERSE()

The syntax for the REVERSE() function is:

REVERSE(str)

The function outputs the str string with a reversed character order. REVERSE() is a multibyte-safe function.

For example:

RIGHT()

The syntax for the RIGHT() function is:

RIGHT(str,len)

The function outputs the rightmost len number of characters from the str string. If any argument is NULL, the result is NULL. RIGHT() is a multibyte-safe function.

For example:

RPAD()

The syntax for the RPAD() function is:

RPAD(str,len,padstr)

The function outputs the specified str string, right-padded with the padstr string, to a length of len characters. The str argument being longer than len shortens the output to len characters.

RPAD() is multibyte safe.

For example:

RTRIM()

The syntax for the RTRIM() function is:

RTRIM(str)

The function outputs the str string without the trailing space characters. The RTRIM() function is multibyte safe.

For example:

SOUNDEX(), i.e., SOUNDS LIKE

The syntax for the SOUNDEX() function is:

SOUNDEX(str)

The function outputs a soundex string, i.e., a phonetic representation of the input str string. The SOUNDEX() function allows users to compare English words that are spelled differently but sound alike.

SOUNDEX() ignores all non-alphabetic characters in the input string and treats all characters outside the A-Z range as vowels.

For example:

The (expr1) SOUNDS LIKE (expr2) function is the same as SOUNDEX(expr1) = SOUNDEX(expr2).

SPACE()

The syntax for the SPACE() function is:

SPACE(N)

The function outputs a string consisting of N number of space characters.

For example:

STRCMP()

The syntax for the STRCMP() function is:

STRCMP(expr1,expr2)

The function compares the two expressions and outputs:

• 0 – If the two expressions are the same.
• -1 – If the first expression is smaller than the second depending on the current sort order.
• 1 – If the second expression is smaller than the first one.

For example:

In this example, the output is 1 because the second argument is smaller than the first one.

SUBSTRING(), i.e., SUBSTR(), MID()

The syntax for the SUBSTRING() function is:

SUBSTRING(str, pos, length)

or:

SUBSTRING(str FROM pos FOR length)

The function extracts a substring from a string, starting at a specified position.

The length argument is optional and used to return a substring length characters long from the str string, starting at pos position.

The pos argument specifies from which position to extract the substring. If pos is a positive number, the function extracts a substring from the beginning of the string. If pos is a negative number, the function extracts a substring from the end of the string.

For example:

MID(str,pos,length) and SUBSTR() are synonyms for SUBSTRING(str,pos,length).

SUBSTRING_INDEX()

The syntax for the SUBSTRING_INDEX() function is:

SUBSTRING_INDEX(str,delim,count)

The function outputs a substring from the str string before a specified count number of delim delimiter occurs.

If the count argument is positive, the function outputs everything left of the final delimiter, counting from the left side.

If the count argument is negative, the function outputs everything right of the final delimiter, counting from the right side.

SUBSTRING_INDEX() searches for the delimiter in a case-sensitive fashion, and it is multibyte safe.

For example:

The example above shows the different outputs when the count argument is positive and negative.

TO_BASE64()

The syntax for the TO_BASE64() function is:

TO_BASE64(str)

The function encodes a string argument to a base-64 encoded form and returns the result. If the argument isn’t a string, the function converts it to a string before base-64 encoding.

If the argument is NULL, the result is NULL.

TO_BASE64() is the reverse of FROM_BASE64().

For example:

The output is a base-64 encoded string.

TRIM()

The syntax for the TRIM() function is:

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

The function removes all remstr prefixes and suffixes from the specified str string and outputs the result.

Unless specifying the BOTH, LEADING,or TRAILING specifiers, the function assumes BOTH.

The remstr argument is optional, and omitting it removes the space characters from the string.

TRIM() is multibyte safe.

For example:

In this example, the function removes the specified leading prefix from the string.

UPPER(), i.e., UCASE()

The syntax for the UPPER() function is:

UPPER(str)

The function changes all characters of the specified str string to uppercase and outputs the result. The default character set mapping it uses is utf8mb4. UPPER() is multibyte safe.

For example:

The UCASE() function is a synonym for UPPER().

UNHEX()

The syntax for the UNHEX() function is:

UNHEX(str)

The function interprets each pair of characters in a string argument as a hexadecimal number and converts it to the byte represented by the number. The output is a binary result.

If the str argument contains non-hexadecimal digits, the output is NULL. A NULL output can also occur if the argument is a BINARY column.

UNHEX() is the opposite of HEX(). However, you shouldn’t use UNHEX() to inverse the HEX() result of numeric arguments. Instead, use the mathematical function CONV(HEX(N),16,10).

For example:

WEIGHT_STRING()

The syntax for the WEIGHT_STRING() function is:

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

• str – The input string argument.
• AS – Optional clause, permits casting the input string to a binary or non-binary string, and to a specific length.
• flags – Optional argument, currently unused.

The function outputs the weight string for the input str string. The output value represents the string’s sorting and comparison value.

If used, the AS BINARY(N) argument measures the length in bytes rather than characters, and right-pads with 0x00 bytes to the specified length.

On the other hand, the AS CHAR(N) argument measures the characters’ length and right-pads with spaces to the specified length.

N has a minimum value of 1. If N is less than the input string length, the string is truncated without issuing a warning.

If the input string is a non-binary value (CHAR, VARCHAR, or TEXT), the output contains the collation weights for the string. If the input string is a binary value (BINARY, VARBINARY, or BLOB), the output is the same as the input string because the weight for each byte in a binary string is the byte value.

If the input string is NULL, the output is NULL.

For example:

In this example, we used HEX() to display the output because HEX() can display binary results containing nonprinting values in a printable form.

Conclusion

You now know the different MySQL string functions and how to use them. Feel free to test them out for yourself to make sure you learn all the ropes.

Find other useful commands in our MySQL Commands Cheat Sheet.