MySQL provides various built-in string functions to perform different operations on string data. In this article, we show you the list of 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.
| Functions | Description |
|---|---|
| ASCII() | It returns the ASCII Numeric Value of the first or left-most character |
| BIN() | It returns a string that contains a Binary representation of a number. |
| BIT_LENGTH() | It returns the length of an argument in bits |
| CHAR() | Returns or converts each integer into a character. |
| CHAR_LENGTH() | This 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 strings with a given separator. |
| ELT() | It returns the string at the index number. |
| EXPORT_SET() | Returns the on the 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 a specified position until it reaches a 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 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 word position. |
| LOWER() | This MySQL function converts a given string into lowercase. |
| LPAD() | It left pads the given text and returns the string value. |
| LTRIM() | Removes the leading spaces. |
| MAKE_SET() | It returns the comma-separated strings set that contain the corresponding bit in bit sets. |
| MATCH() | Use this method to perform a full-text search. |
| MID() | It returns a substring that starts at the 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 an 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 sentence. |
| 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 repeats the text for the specified number of times. |
| REPLACE() | Find and replace a word with the specified sentence. |
| REVERSE() | It reverses 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 appends text for a 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 a given number of spaces. |
| STRCMP() | This method compares two strings. |
| SUBSTR() | Returns the substring of a given |
| 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 values to return the substring. |
| TRIM() | It removes leading and trailing spaces from a string. |
| UCASE() | Synonym of UPPER() function. |
| UNHEX() | It returns a string that contains the Hex representation of a number. |
| UPPER() | This method is used to convert to Uppercase |
MySQL String Functions Examples
The following examples help you to understand these string functions.
ASCII, BIN, BIT_LENGTH Example
In this example, we are going to use MySQL 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');
In this example, we used the MySQL CHAR, CHAR_LENGTH, and CHARACTER_LENGTH methods to find the character for the ASCII code length of a character in MySQL.
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');
CONTACT and CONTACT_WS Example 3
In this example, we used CONCAT and CONCAT_WS methods to concat multiple string values. Next, we used the ELT to find the word at the 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');
In this example, we are going to use the MySQL 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');
MySQL String Functions Example 5
In this example, we used the MySQL 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');
In this instance, we used this INSTR method to find the index position of the first occurrence of a substring. Next, we used the LCASE to convert the text to lowercase and LEFT 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);
In this example, we used the LENGTH function to find the string length. Next, we used LOCATE to find the index position of the first occurrence of a substring. Within the last statement, the LOWER function converts the given sentence 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');
MySQL String Functions Example 8
In this example, we used MySQL LPAD to append substring to the left side of a sentence and LTRIM to remove leading spaces. Next, we used MySQL RTRIM to remove the trailing spaces.
SELECT LPAD('MySQL', 7, 'Hi'), LPAD('MySQL', 3, 'Hi');
SELECT LTRIM(' Hi'), LTRIM(' MySQL');
SELECT RTRIM('Hello '), RTRIM('MySQL ');
In this string function example, we used the 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);
MySQL String Functions Example 10
In this example, we used MySQL REPEAT to repeat the sentence REPLACE function to replace the word with a new substring. Next, we used the REVERSE function to reverse the given sentence.
SELECT REPEAT('MySQL', 2), REPEAT('Hello', 5);
SELECT REPLACE('hello', 'l', 'd'), REPLACE('tutorialgateway', 't', 'AA');
SELECT REVERSE('hello'), REVERSE('MySQL'), REVERSE('Suresh');
MySQL RIGHT, RPAD, SOUNDEX Example 11
In this example, we are going to use the RIGHT function to find the rightmost characters and the RPAD to append substring to the right side. Next, we used SOUNDEX to find the Soundex value of a given text.
SELECT RIGHT('tutorialgateway', 4), RIGHT('tutorialgateway', 10);
SELECT RPAD('MySQL', 10, 'Hello'), RPAD('MySQL', 3, 'Hi');
SELECT SOUNDEX('Hello'), SOUNDEX('MySQL');
In this scenario, we used MySQL SPACE to generate empty spaces. Next, we used MySQL SUBSTR and SUBSTRING to return the substring from a given text.
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);
MySQL String Functions Example 13
In this example, we used the MySQL TRIM to remove leading and trailing spaces. Next, the MySQL UCASE converts text 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');
Here, we used the MySQL UNHEX to return the text from the hex number. Next, we used MySQL UPPER to convert the text to uppercase. The last QUOTE statement is 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!');
