MySQL String Functions

In this post, I am sharing few essential string manipulation functions of MySQL.

Below is a list of String functions with an example.

CONCAT (Str1,Str2…):This function is used to concat two or more string and integer by passing a string or integer arguments. If you try to concat NULL with string, the result will be NULL. You can also concat multiple arguments using a single function.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

mysql> SELECT CONCAT('String',' Function') AS CONCAT; +-----------------+ | CONCAT          | +-----------------+ | String Function | +-----------------+   mysql> SELECT CONCAT('String',' Function',' Demo') AS CONCAT; +----------------------+ | CONCAT               | +----------------------+ | String Function Demo | +----------------------+   mysql> SELECT CONCAT('String',' Function',null) AS CONCAT; +--------+ | CONCAT | +--------+ | NULL   | +--------+   mysql> SELECT CONCAT('String',' Function ',10) AS CONCAT; +--------------------+ | CONCAT             | +--------------------+ | String Function 10 | +--------------------+

LEFT (str,len): This will return left most character from string and if you pass NULL argument, returns NULL result.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> SELECT LEFT ('String Function',5);   +----------------------------+ | LEFT ('String Function',5) | +----------------------------+ | Strin                      | +----------------------------+   mysql> SELECT LEFT (15245,3); +----------------+ | LEFT (15245,3) | +----------------+ | 152            | +----------------+

RIGHT (str,len) : This will return right most character from string and if you pass NULL argument, returns NULL result.

1 2 3 4 5 6 7

mysql> SELECT RIGHT('String Function',5);   +----------------------------+ | RIGHT('String Function',5) | +----------------------------+ | ction                      | +----------------------------+

INSTR (str,substr) : This function is used to find starting position of given substring.

1 2 3 4 5 6

mysql> SELECT INSTR('String Function','Fun'); +--------------------------------+ | INSTR('String Function','Fun') | +--------------------------------+ |                              8 | +--------------------------------+

LOCATE (substr,str) : This function work same as INSTR(str,substr)

1 2 3 4 5 6

mysql> SELECT LOCATE('Fun','String Function'); +---------------------------------+ | LOCATE('Fun','String Function') | +---------------------------------+ |                               8 | +---------------------------------+

POSITION (substr IN str) : This function work same as INSTR(str,substr)

1 2 3 4 5 6

mysql> SELECT POSITION('Fun' IN 'String Function'); +--------------------------------------+ | POSITION('Fun' IN 'String Function') | +--------------------------------------+ |                                    8 | +--------------------------------------+

INSERT(str,pos,len,newstr) : This function is used to overwrite new string part on an old string part.
You require passing position and length to replace old part of string using the new part of a string.

1 2 3 4 5 6

mysql> SELECT INSERT('String Function',7,4,'Demo'); +--------------------------------------+ | INSERT('String Function',7,4,'Demo') | +--------------------------------------+ | StringDemoction                      | +--------------------------------------+

LENGTH(str): This function is used to find length of given string.

1 2 3 4 5 6

mysql> SELECT LENGTH('String Function'); +---------------------------+ | LENGTH('String Function') | +---------------------------+ |                        15 | +---------------------------+

UPPER(str) / LOWER(str) : This both function is used to change string from lower to upper or upper to lowar case.

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> SELECT LOWER('FUNCTION'); +-------------------+ | LOWER('FUNCTION') | +-------------------+ | function          | +-------------------+   mysql> SELECT UPPER('function'); +-------------------+ | UPPER('function') | +-------------------+ | FUNCTION          | +-------------------+

REPEAT (str,count): This function is used to repeat string by given count.

1 2 3 4 5 6

mysql> SELECT REPEAT(' String Function',3); +-----------------------------------------------------+ | REPEAT(' String Function',3)                        | +-----------------------------------------------------+ |  String Function String Function String Function    | +-----------------------------------------------------+

REPLACE(str,from_str,to_str): This function is used to replace a substring by the new string. You can replace any part of string by giving from_string and to_string.

1 2 3 4 5 6

mysql> SELECT REPLACE('String Function','Function','Demo'); +----------------------------------------------+ | REPLACE('String Function','Function','Demo') | +----------------------------------------------+ | String Demo                                  | +----------------------------------------------+

REVERSE(str) : This function is used to reverse each word of string.

1 2 3 4 5 6

mysql> SELECT REVERSE ('String Function'); +-----------------------------+ | REVERSE ('String Function') | +-----------------------------+ | noitcnuF gnirtS             | +-----------------------------+

STRCMP(str1,str2): This function is used to compare two string. This is not case sensitive. If both strings match, then it returns 0 else it returns 1 or -1 base on how first string is bigger than the second string.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

mysql> SELECT STRCMP('String','String'); +---------------------------+ | STRCMP('String','String') | +---------------------------+ |                         0 | +---------------------------+   mysql> SELECT STRCMP('STRING','string'); +---------------------------+ | STRCMP('STRING','string') | +---------------------------+ |                         0 | +---------------------------+   mysql> SELECT STRCMP('STRING','Function'); +-----------------------------+ | STRCMP('STRING','Function') | +-----------------------------+ |                           1 | +-----------------------------+   mysql> SELECT STRCMP('String', 'tring'); +---------------------------+ | STRCMP('String', 'tring') | +---------------------------+ |                        -1 | +---------------------------+

SUBSTRING (str,pos) : This function is used to return sub part of string by giving position.

1 2 3 4 5 6

mysql> SELECT SUBSTRING('String Function',5); +--------------------------------+ | SUBSTRING('String Function',5) | +--------------------------------+ | ng Function                    | +--------------------------------+

SUBSTRING (str,pos,len): This function is used to return sub part of string by giving position and length of return string.

1 2 3 4 5 6

mysql> SELECT SUBSTRING('String Function',5,8); +----------------------------------+ | SUBSTRING('String Function',5,8) | +----------------------------------+ | ng Funct                         | +----------------------------------+

TRIM (str): This function is used to remove all prefixes or suffixes from string. You can also use LTRIM(str) and RTRIM(str) to remove space from the left or right only.

1 2 3 4 5 6

mysql> SELECT TRIM(' Test '); +----------------+ | TRIM(' Test ') | +----------------+ | Test           | +----------------+

Above all are basic and important String manipulation function in MySQL.