String Functions in MySql
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(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,...) :--> 9
mysql> select character_length("easycalculation");
--> 15
Returns the concatenated string of the given arguments.
mysql> select concat('hiox','india');
--> 'hioxindia'
CONCAT_WS() :--> '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)
FORMAT() :--> '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() :--> '12,332.1235'
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. --> 'hiox'
mysql> select lower('EASYCALCULATION');
--> easycalculation
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) :--> 9
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) :--> 5
mysql> select position('cul' in 'easycalculation');
--> 8
The given string is repeated for the given count.
mysql> select repeat('HIOX',2);
--> 'HIOXHIOX'
REPLACE(str,from_str,to_str) :--> 'HIOXHIOX'
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').--> youMYSql
REVERSE(str) :
The given string is reversed and returned.
mysql> select reverse('HIOX');
--> 'XOIH'
SUBSTRING(str,pos) :--> 'XOIH'
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'
--> 'CALCULATION'
