MySQL String Functions

MySQL provides various built-in string functions to perform different operations on string data. In this article, we 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 Examples

The following examples help you to understand these string functions.

MySQL String Functions Example 1

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');

OUTPUT

String Functions Example 2

In this example, we used the MySQL 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 MySQL CONCAT and CONCAT_WS functions to concat multiple string values. Next, we used the ELT to find the string 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');

OUTPUT

String Functions Example 4

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');

OUTPUT

MySQL String Functions Example 5

In this example, we used 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');

OUTPUT

String Functions Example 6

In this instance, we used this MySQL INSTR function to find the index position of the first occurrence of a substring. Next, we used the MySQL 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

String Functions Example 7

In this MySQL string functions 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 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 the left side of a string, and LTRIM to remove leading spaces. Next, we used 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         ');

OUTPUT

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 MySQL REPEAT function to repeat the string, REPLACE function to replace the string with a 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 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 string.

SELECT RIGHT('tutorialgateway', 4), RIGHT('tutorialgateway', 10);

SELECT RPAD('MySQL', 10, 'Hello'), RPAD('MySQL', 3, 'Hi');

SELECT SOUNDEX('Hello'), SOUNDEX('MySQL');

OUTPUT

String Functions Example 12

In this scenario, we used the MySQL SPACE function to generate empty spaces. Next, we used the MySQL SUBSTR and SUBSTRING 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, we used the MySQL TRIM to remove leading and trailing spaces. Next, the MySQL 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

String Functions Example 14

Here, we used the MySQL UNHEX to return the string from the hex number. Next, we used MySQL UPPER to convert the string 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!');

OUTPUT