String Fuctions in MySQL

Tutorials

Mysql

Topic

How to use mysql string functions ?

Explanation

String Function In MySQL:

Functions are predefined set of instructions that returns a value. Functions which involves strings are called as String functions. There are different types of functions availble in MySQL.

The important MySQL string functions are,

CHAR_LENGTH
CONCAT
CONCAT_WS
FORMAT
LCASE
LENGTH
LOCATE
REPEAT
REPLACE
REVERSE
SUBSTRING

CHAR_LENGTH(str) or CHARACTER_LENGTH(str) :

This string function returns the length of the string.

mysql> select char_length("hioxindia");
 --> 9

mysql> select character_length("easycalculation");
 --> 15

CONCAT(str1,str2,...) :

Returns the concatenated string of the given arguments.

mysql> select concat('hiox','india');
 --> 'hioxindia'

CONCAT_WS() :

It stands for Concatenate With Separator and is a special form of CONCAT function. Returns the concatenated string of the given arguments seperated by given seperator.

mysql> SELECT CONCAT_WS('!','One','Two','Three');
 --> 'One!Two!Three'
( Here '!' is the seperator)

FORMAT() :

Formats the given no and rounds to the given digits after decimal point.

mysql> SELECT FORMAT(12332.123456, 4);
 --> '12,332.1235'

LCASE(str) or LOWER() :

Returns the lowercase of the given string.

mysql> select lcase('HIOX');
 --> 'hiox'

mysql> select lower('EASYCALCULATION');
 --> easycalculation

    Like wise UPPER or UCASE returns the uppercase of the given string.

LENGTH(str) :

Returns the length of the given string in bytes. If there is a 2 byte character the length is calculated as 2. Whereas the CHAR_LENGTH calculates only the character length.

mysql> select length('HIOXINDIA');
 --> 9

LOCATE(substr,str) or POSITION(substr IN Str) :

Returns the position of the first occurance of the substring in the string.

mysql> select locate('ind','hioxindia');
 --> 5

mysql> select position('cul' in 'easycalculation');
 --> 8

REPEAT(str,count) :

The given string is repeated for the given count.

mysql> select repeat('HIOX',2);
 --> 'HIOXHIOX'

REPLACE(str,from_str,to_str) :

In the given string 'str' the 'from_str' is replaced by the 'to_str' string.

mysql> select replace('MyMYSql','My','you');
 --> youMYSql

The given 'from_str' is case sensitive. Here in the above example the first 'My' is changed but not the second('MY').

REVERSE(str) :

The given string is reversed and returned.

mysql> select reverse('HIOX');
 --> 'XOIH'

SUBSTRING(str,pos) :

The function returns a substring from the string 'str' starting at position 'pos'. The above descriptions shows the uses of String Functions in MySQL.

mysql> select substring('EASYCALCULATION', 5);
 --> 'CALCULATION'

String Functions in MySql