String Functions in MySql

How to use mysql string functions ?


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

Ask Questions

Ask Question