MySQL provides various built-in string functions to perform different operations on string data. In this article, we will show you the list of MySQL string functions, and an example of each one of them.
MySQL String Functions
The following are the list of MySQL String functions that are available to work with String data.
| MySQL String Functions | Description |
|---|---|
| ASCII() | It returns the ASCII Numeric Value of the first or left most character |
| BIN() | It returns a string that contains Binary representation of a number. |
| BIT_LENGTH() | This returns length of an argument in bits |
| CHAR() | Returns or converts each integer into a character. |
| CHAR_LENGTH() | This MySQL String function returns the total number of characters in a given argument. |
| CHARACTER_LENGTH() | Synonym for the CHAR_LENGTH() function |
| CONCAT() | It returns the concatenated string |
| CONCAT_WS() | WS means with separator. It concatenates string with given separator. |
| ELT() | It returns string at index number. |
| EXPORT_SET() | Returns an on string for every bit set in a value set, and for every unset bit this function returns an off string. |
| FILED() | This function returns the Index position of the first argument in the subsequent arguments. |
| FIND_IN_SET() | It returns the Index position of the first argument within the second argument. |
| FORMAT() | Formats the number as per the user specified number of decimal places. |
| HEX() | Hexadecimal representation of a string value or decimal value. |
| INSERT() | This MySQL String function inserts a substring at specified position until it reaches to specified number of characters. |
| INSTR() | It returns the index position of the first occurrence of a Substring. |
| LCASE() | Synonym of LOWER() function. |
| LEFT() | Returns the leftmost characters specified by the user. |
| LENGTH() | This function returns the string length in bytes. |
| LIKE | Use this for pattern matching |
| LOAD_FILE() | It loads the specified file. |
| LOCATE() | Find and returns the first occurrence of a string position. |
| LOWER() | This MySQL String function converts a given string into lowercase. |
| LPAD() | It left pads the given string and returns the string value. |
| LTRIM() | Removes the leading spaces. |
| MAKE_SET() | It returns the comma separated strings set that contains the corresponding bit in bit sets. |
| MATCH() | Use this function to perform full text search. |
| MID() | It retunes a substring which starts at user given position. |
| NOT_LIKE | This is a Negation of LIKE function (simple pattern matching) |
| NOT_REGEXP | This is a Negation of REGEXP function |
| OCT() | It returns a string that contains Octal representation of a number. |
| OCTET_LENGTH() | Synonym of LENGTH() function |
| ORD() | This MySQL String function returns the character code of a leftmost character in a given string. |
| POSITION() | Synonym of LOCATE() function |
| QUOTE() | It escapes the argument for use in a SQL Statement. |
| REGEXP | Whether the string matches the regular expression. |
| REPEAT() | This will repeats the string for specified number of times. |
| REPLACE() | Find and replace a string with specified string |
| REVERSE() | It reverse the given string (total characters) |
| RIGHT() | Returns the right most characters specified by the user. |
| RLIKE | Use this to check whether the string matches the regular expression. |
| RPAD() | It append string for user specified number of times |
| RTRIM() | This MySQL String function removes trailing spaces. |
| SOUNDEX() | This returns a soundex string |
| SOUNDS LIKE | It compares sounds |
| SPACE() | It provides or adds given number of spaces. |
| STRCMP() | This function compares two strings. |
| SUBSTR() | Returns the substring of a given string |
| SUBSTRING() | As the name suggests, it returns the substring of a given string |
| SUBSTRING_INDEX() | It returns a substring from a given string. It uses the delimiter and the number of occurrence value to return the substring |
| TRIM() | This MySQL String function removes leading and trailing spaces from a string. |
| UCASE() | Synonym of UPPER() function. |
| UNHEX() | It returns a string that contains Hex representation of a number. |
| UPPER() | This function is used to convert to Uppercase |
MySQL String Functions Examples
The following examples helps you to understand these string functions in MySQL
MySQL String Functions Example 1
In this example, we are going to use the ASCII, BIN, and BIT_LENGTH functions.
SELECT ASCII(5), ASCII('T'), ASCII('Tut'), ASCII('a');
SELECT BIN(10), BIN(25), BIN(34), BIN(8), BIN(4);
SELECT BIT_LENGTH('Hi'), BIT_LENGTH('Tutorial');
OUTPUT
MySQL String Functions Example 2
In this example, we used the CHAR, CHAR_LENGTH and CHARACTER_LENGTH functions to find the character for ASCII code, length of a character.
SELECT CHAR(84), CHAR(77, 121, 83, 81, 76), CHAR(72, 105);
SELECT CHAR_LENGTH('Hi'), CHAR_LENGTH('MySQL');
SELECT CHARACTER_LENGTH('Hello'), CHARACTER_LENGTH('Tutorial');
OUTPUT
MySQL String Functions Example 3
In this example, we used the CONCAT and CONCAT_WS functions to concat multiple string values. Next, we used the ELT to find to find the string at specified index.
SELECT CONCAT('Hello', 'World'), CONCAT('Hi', 'All');
SELECT CONCAT_WS(',','Hello', 'World'), CONCAT_WS(',','Hi', 'All');
SELECT ELT(2, 'aa','bb','cc','dd'), ELT(4, 'aa','bb','cc','dd');
OUTPUT
MySQL String Functions Example 4
In this MySQL string functions example, we are going to use the EXPORT_SET, FIELD, and FIND_IN_SET functions.
SELECT EXPORT_SET(5, 'Y', 'N', ',', 4), EXPORT_SET(5, 'Y', 'N', '-', 6);
SELECT FIELD('abc', 'xyz', 'abc', 'Hi', 'you', 'abc');
SELECT FIND_IN_SET('x', 'a,b,c,x,d,e'), FIND_IN_SET('x', 'a,x,c,x,d,e');
OUTPUT
MySQL String Functions Example 5
In this example, we used FORMAT function to format the value, HEX to find the hex value. Next, we used the INSERT function to insert the substring.
SELECT FORMAT(98765.126349, 3), FORMAT(98765.126349, 2, 'ru_RU');
SELECT HEX('Hi'), HEX(25), HEX(255), HEX(495), HEX(1235);
SELECT INSERT('TutorialGateway', 4, 2, 'aaa');
OUTPUT
MySQL String Functions Example 6
In this example, we used this INSTR function to find the index position of a first occurrence of a substring. Next, we used the LCASE to convert the string to lowercase, and LEFT function to find the leftmost characters in a string.
SELECT INSTR('hiabcatabc', 'abc'), INSTR('we abc at abc in xyz', 'abc');
SELECT LCASE('Hi GuYS'), LCASE('MySQL'), LCASE('SUResH');
SELECT LEFT('tutorialgateway', 8), LEFT('tutorialgateway', 12);
OUTPUT
MySQL String Functions Example 7
In this MySQL string functions example, we used LENGTH function to find the string length. Next, we used LOCATE to find the index position of a first occurrence of a substring. Within the last statement, LOWER function is used to convert the given string to lowercase.
SELECT LENGTH('hello'), LENGTH('Hello world');
SELECT LOCATE('at','abcatabc'), LOCATE('abc','we abc at abc in xyz', 7);
SELECT LOWER('HELLO WoRLd'), LOWER('MySQL TUTORIAL'), LOWER('SUResH');
OUTPUT
MySQL String Functions Example 8
In this example of MySQL string functions, we used LPAD to append substring to left side of a string, LTRIM to remove leading spaces. Next, we used RTRIM to remove the tailing spaces.
SELECT LPAD('MySQL', 7, 'Hi'), LPAD('MySQL', 3, 'Hi');
SELECT LTRIM(' Hi'), LTRIM(' MySQL');
SELECT RTRIM('Hello '), RTRIM('MySQL ');
OUTPUT
MySQL String Functions Example 9
In this example of MySQL string functions, we used MAKE_SET function, MID and OCT functions.
SELECT MAKE_SET(2, 'aa', 'bb', 'cc'), MAKE_SET(1|3, 'aa', 'bb', 'cc');
SELECT MID('tutorialgateway', 2, 5), MID('tutorialgateway', 5, 13);
SELECT OCT(15), OCT(2), OCT(255);
OUTPUT
MySQL String Functions Example 10
In this example, we used REPEAT function to repeat the string, REPLACE function to replace the string with new string. Next, we used the REVERSE function to reverse the given string.
SELECT REPEAT('MySQL', 2), REPEAT('Hello', 5);
SELECT REPLACE('hello', 'l', 'd'), REPLACE('tutorialgateway', 't', 'AA');
SELECT REVERSE('hello'), REVERSE('MySQL'), REVERSE('Suresh');
OUTPUT
MySQL String Functions Example 11
In this example, we are going to use the RIGHT function to find rightmost characters, RPAD to append substring to right side. Next, we used SOUNDEX to find the soundex value of a given string.
SELECT RIGHT('tutorialgateway', 4), RIGHT('tutorialgateway', 10);
SELECT RPAD('MySQL', 10, 'Hello'), RPAD('MySQL', 3, 'Hi');
SELECT SOUNDEX('Hello'), SOUNDEX('MySQL');
OUTPUT
MySQL String Functions Example 12
In this example, we used the SPACE function to generate empty spaces. Next, we used SUBSTR and SUBSTRING function to return the substring from a given string.
SELECT SPACE(5), CONCAT('Learn', SPACE(2), 'MySQL');
SELECT SUBSTR('tutorialgateway', 9), SUBSTR('MySQL Tutorial', 3, 9);
SELECT SUBSTRING('tutorialgateway', 9), SUBSTRING('MySQL Tutorial', 3, 9);
OUTPUT
MySQL String Functions Example 13
In this example of MySQL string functions, we used the TRIM to remove leading and trailing spaces. Next, UCASE to convert string to Uppercase and SUBSTRING_INDEX to return substring upto index position of a specified delimiter.
SELECT SUBSTRING_INDEX('hi,how are,you', ',', 2);
SELECT TRIM(' hi '), TRIM(BOTH 'A' FROM 'AAAAhello worldAAAA');
SELECT UCASE('suREsh'), UCASE('TutoriALgatEway');
OUTPUT
MySQL String Functions Example 14
Here, we used the UNHEX to return the string from hex number. Next, we used UPPER to convert the string to uppercase. Last QUOTE statement is used to skip or escape special characters.
SELECT UNHEX('4869'), UNHEX('255'), UNHEX('4D7953514C');
SELECT UPPER('Hi'), UPPER('HeLlo world'), UPPER('mysql');
SELECT QUOTE('Don\'t Worry!');
OUTPUT
Thank You for Visiting Our Blog
