MySQL provided various functions to interact and modify the strings passed via arguments or fetched from tables. This article will go through all the available MSQL String Functions. Before that, let’s understand how exactly a string is treated in MySQL.

MySQL String

When a binary string is passed to a string function as an argument, the result is also a binary string. A binary string is a number that has been transformed into a string. Only comparisons are affected by this.

If any expression in a string comparison is case-sensitive, the comparison is normally conducted in that manner.

Binary strings are shown using hexadecimal notation whenever a string function is called from within the MySQL client, depending on the value of the — binary-as-hex option.

String CONCAT_WS() function

CONCAT WS() is a special form of CONCAT that stands for Concatenate With Separator (). The first argument serves as a divider between the other arguments. Between the strings to be concatenated, a separator is added. Like the rest of the inputs, the separator can be a string. The result is NULL if the separator is NULL.

mysql> SELECT CONCAT_WS(‘,’,’First name’,’Second name’,’Last Name’);

Output: ‘First nameSecond nameLast Name’

String CONCAT() function

Concatenates the parameters and returns the resultant string. There could be one or many arguments. The result is a nonbinary string if all parameters are nonbinary strings. The result is a binary string if any of the arguments are binary strings. The comparable nonbinary string form of a numeric argument is converted.

If any of the arguments are NULL, CONCAT() returns NULL.

mysql> SELECT CONCAT(‘My’, ‘S’, ‘QL’);

Output: ‘MySQL’

String CHARACTER_LENGTH() function

The length of the string str in code points is returned. A single code point is assigned to a multibyte character. This means that LENGTH() returns 6 for a string containing two 3-byte characters, whereas CHAR LENGTH() returns 2, as illustrated here:

mysql> SET @dolphin:=’海豚’;

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT LENGTH(@dolphin), CHAR_LENGTH(@dolphin);

+——————+———————–+

| LENGTH(@dolphin) | CHAR_LENGTH(@dolphin) |

+——————+———————–+

| 6 | 2 |

+——————+———————–+

1 row in set (0.00 sec)

String ELT() function

MySQL’s Elt() function is a Sring function. This method returns the Nth entry from a string list.

Example

select elt(3,’this’,’is’,’GoCoding’,’website’)

Output:

GoCoding

String EXPORT_SET() function

MySQL’s export set() function is a String function. For each bit set, this procedure returns a string.

select export_set(5, ‘Yes’,’No’,’,’,4);

Output:

Yes,No,Yes,No

String FIELD() function

MySQL’s Field(str,str1,str2,str3,…) function is a Sring function. This method returns the string’s index.

Select field(‘a’,’b’,’c’,’d’,’a’);

Output:

4

String FIND_IN_SET() function

MySQL’s Find in set(str,strlist) function is a Sring function. The value of the string in the first position in the argument is returned by this method.

Select find_in_set(‘n’,’gocoding’);

Output:

7

String FORMAT() function

MySQL’s String function Format(X,D[,locale]) is a String function. This approach rounds the number X to the decimal place of D.

Select format(12345.6789,3);

Output:

12,345.679

String FROM_BASE64() function

MySQL’s String function Format(X,D[,locale]) is a String function. This approach rounds the number X to the decimal place of D.

Select from_base64(‘java’) as ‘Input’;

Output:

String HEX() function

MySQL’s Hex() function is a Sring function. This method returns the supplied integer or String as a hexadecimal string.

Select hex(‘a’);

Output:

61

String INSERT() function

MySQL’s INSERT(str,pos,len,newstr) function is a Sring function. A string str is supplied to this function with a position ‘pos’ that specifies where the character should be placed and a length ‘len’ that specifies the length of the character to be placed, i.e. string newstr.

Select insert(‘GoCoding’,5,1,’t’);

Output:

‘GoCoding’

String INSTR() function

INSTR(str,substr) is a MySQL Sring function. This method returns the substr’s (string 2) first occurrence in string str (string 1).

Select instr(‘GoCoding’,’Code’);

Output:

3

String LCASE() function

LCASE(str) is a MySQL String function. This method returns a lowercase string.

select lcase(‘GOCODING’);

Output:

gocoding

Note: The function LOWER() is similar to this function. We will discuss it too later.

String LEFT() function

select left(‘GoCoding’,2);

Output:

Go

String LENGTH() function

LENGTH(str) is a MySQL String function. The length of the given string?str? in bytes is returned by this method.

select LENGTH(GoCoding);

Output:

8

String like() function

Select ‘GoCoding’ like ‘gocoding’;

Output:

1

String LOAD_FILE() function

MySQL’s LOAD FILE(file name) function is a String function. This method returns the file’s content. It returns NULL if the file does not exist or cannot be read.

String LOCATE() function

LOCATE(substr,str) is a MySQL Sring function. The first occurrence of the supplied substr in the given string is returned by this method.

select locate(‘Coding’, ‘GoCoding’);

Output:

6

String LOWER() function

MySQL’s LOWER(str) function is a Sring function. The specified string is returned in lower case by this method.

Select lower(‘GOCODING’);

Output:

‘gocoding’

String LPAD() function

MySQL’s LPAD(str,len,padstr) function is a Sring function. This method returns the string’str,’ padded to the specified length.

Select lpad(‘gocoding’, 2, ‘**’);

Output:

go

String LTRIM() function

LTRIM(str) is a MySQL String function. This method removes the leading space from the string and returns it.

Select ltrim(‘    GoCoding’);

Output:

GoCoding

String MAKE_SET() function

MySQL’s MAKE SET(bits,str1,str2,…) function is a Sring function. For the provided bit, this method retrieves values from set.

Select make_set(1, ‘apple’,’mango’,’grapes’);

Output:

apple

String MID() function

MySQL’s MID(str,pos,len) function is a Sring function. This method returns a string with the length and position you specify.

Select mid(‘mysql’, 3, 4);

Output:

sql

String OCTET_LENGTH() function

OCTET LENGTH(str) is a MySQL Sring function. The length of a string is returned by this method.

select octet_length(‘GoCoding’);

Output:

10

String OCT() function

MySQL’s Oct(n) function is a Sring function. The octal value of a given number is returned by this procedure (n).

select oct(10);

Output:

12

String ORD() function

ORD(str) is a MySQL Sring function. The code for the given string is returned by this procedure.

Select ord(‘mysql’);

Output:

109

String POSITION() function

MySQL’s POSITION(substr IN str) function is a String function. This function returns the substring’s position in the string.

select position(‘point’ in ‘GoCoding’);

Output:

6

String QUOTE() function

MySQL’s String function quotes(str). The string supplied in single quotes is returned by this procedure.

select quote(‘GoCoding is a website’);

Output:

‘GoCoding is a website’

String REPEAT() function

REPEAT(str,count) is a MySQL String function. The string reputation counter times is returned by this procedure.

select repeat(‘GoCoding ‘, 4);

Output:

GoCoding GoCoding GoCoding GoCoding

String REPLACE() function

MySQL’s REPLACE(str,from str,to str) function is a String function. This method converts a string into a string from a specified string.

Syntax: REPLACE(str_which_needs_replacement,str_that_will_be_replaced,str_that_will_be_replacing)

select replace(‘XYZ’, ‘X’, ‘A’);

Output:

AYZ

String REVERSE() function

MySQL’s REVERSE() function is a String function. This method reverses the order of the given string.

Select reverse(‘don’);

Output:

nod

String RIGHT() function

MySQL’s Right() function is a String function. This method returns the ability to extract a substring from a string, starting from the right side.

Select right(‘GoCoding’, 6);

Output:

Coding

String RPAD() function

The RPAD() is a String function of MySQL. This method returns the given string which is right-padded with the string padstr to the given length (len).

Select rpad(‘GoCoding’, 4, ‘**’);

Output:

GoCo

String RTRIM() function

MySQL’s Rtrim() function is a String function. If there is any extra space in the string’s tail, this technique removes it.

Select rtrim(‘GoCoding   ‘);

Output:

GoCoding

String SOUNDEX() function

MySQL’s Soundex() function is a String function. A Soundex string is returned by this procedure. Soundex strings should be identical for two strings that sound almost identical.

Select soundex(‘GoCo’);

Output:

3131

String SPACE() function

In MySQL, the SPACE() method returns a string containing the provided empty space characters.

SELECT ‘start’, SPACE(10), ‘end’;

Output:

+——-+————+—–+

| start | SPACE(10) | end |

+——-+————+—–+

| start | | end |

+——-+————+—–+

String STRCMP() function

Required to The strings that will be compared

SELECT STRCMP(“SQL Tutorial”, “SQL Tutorial”);

Output:

0

String SUBSTR() function

Remove a substring from a string (start at position 5 and remove 3 characters):

SELECT SUBSTR(“SQL Tutorial”, 5, 3) AS ExtractString;

String SUBSTRING_INDEX() function

Before a specified number of delimiters, return a substring of a string:

String SUBSTRING() function

Start at position 5 and extract 3 characters from a string:

SELECT SUBSTRING(“SQL Tutorial”, 5, 3) AS ExtractString;

Output:

Tut

String Trim() function

To remove leading and trailing spaces from a string, follow these steps:

String UCASE() function

Covert input string into uppercase

SELECT UCASE(“SQL Tutorial is FUN!”) AS UppercaseText;

Output:

SQL TUTORIAL IS FUN!

String UNHEX() function

The UNHEX() function in MySQL is used to convert a Hexadecimal number into bytes that can be represented by a Number. It returns a binary string as a result.

SELECT UNHEX(‘48656C6C6F’) AS String_Name;

Output:

String_Name

Hello

String UPPER() function

Convert the string function into upper case

SELECT UPPER(“SQL Tutorial is FUN!”);

Output:

SQL TUTORIAL IS FUN!