In MySQL String Functions Part-2 I shall discuss the CONCAT function, CONCAT_WS function, GROUP_CONCAT function, ELT function, and EXPORT_SET function.
CONCAT Function:- CONCAT function adds or concatenate two or more argument or expression. If any argument is a NULL value then It returns NULL. If any argument is a binary value It returns Binary. If any argument is a numeric value then it returns its equivalent nonbinary string.
Syntax:- CONCAT(expr1, expr2, expr3,….);
For Example:-
SELECT CONCAT(‘Ram ‘, ‘is ‘, ‘a ‘, ‘good ‘, ‘boy.’) AS ConcatenatedString ;
Output:- Ram is a good boy.
If some tables have firstName, LastName has two different columns. and we want the output should come in a single column with the column name FullName.
SELECT CONCAT(firstName, ” “, LastName) AS FullName FROM student;
SELECT CONCAT(15, 35, 7.30) AS ConcatNumber ;
Output:- 15357.30
SELECT CONCAT(‘ksh’, ‘tutor’, NULL) AS ConcatString ;
Output:- NULL
CONCAT_WS Function:- CONCAT_WS function adds or concatenate two or more argument or expression with separators. Separators are always specified as the first argument. The separator can be a string but cannot be NULL If the separator is NULL the result is NULL.
Syntax:- CONCAT_WS(separator, expr1, expr2, expr3,….);
For Example:-
SELECT CONCAT_WS(“-“, “Mysql”, “is”, “easy”, “to”, “learn”);
Output:- Mysql-is-easy-to-learn
If some tables have firstName, LastName has two different columns. and we want the output should come in a single column with the column name FullName.
SELECT CONCAT_WS(” “, firstName, LastName) AS FullName FROM student;
GROUP_CONCAT Function:- GROUP_CONCAT is a function that concatenates or merges the data from multiple rows into one field. This is an aggregate (GROUP BY) function that returns a String value. It returns NULL when there are no non-NULL values.
GROUP_CONCAT() function returns a string with the concatenated non-NULL value from a group.
CONCAT_WS() function is used to add two or more expressions with a separator.
Syntax:- GROUP_CONCAT(exp1, exp2, exp3…. ORDER BY expr SEPARATOR);
For Example:- Suppose We have a people table having field Name, country, quality
| Name | Country | Quality |
| A | USA | Sharp |
| B | IN | Good |
| C | UK | Gentle |
| A | USA | Good |
| C | UK | Fair |
| B | IN | Intelligent |
SELECT Name, Country, GROUP_CONCAT(Quality) as “GoodQuality” FROM people GROUP BY Name;
OUTPUT:-
A USA sharp, good
B IN good, intelligent
C UK gentle, fair
ELT Function:- ELT function return those string that specifies in the index number. It returns a string at the specified index. If the INDEX number will be NULL, that means either the index number is less than 1 or it is greater than the specified string set.
Note:- ELT, stands for “Extract, Load, Transform,” data integration process.
Syntax:- ELT(INDEX, str1, str2, str3…..);
Example:-
SELECT ELT(2, ‘cat’, ‘dog’,’ bee’, ‘hen’);
Output:- dog
Here the second element is extracted.
SELECT ELT(5, ‘cat’, ‘dog’,’ bee’, ‘hen’);
Output:- NULL
The index number is more than the listed string
SELECT ELT(0, ‘cat’, ‘dog’, ‘bee’, ‘hen’);
Output:- NULL
The index number is less than 0
EXPORT_SET Function:- EXPORT_SET function returns the string for each bit set.
Syntex:- EXPORT_SET( bits, on, off, separator, number_of_bits )
Bits is an integer value That will be supplied their binary representation 0 and1
on represent to return the first argument if the bit value will be 1
off represent to return 2nd argument if the bit value will be 0
A number of bits are an integer and its default value will be 64. If the Number of bits value will be larger than 64 then it clipped to 64.
For Example:-
SELECT EXPORT_SET ( 7, ‘a’, ‘b’, ‘,’, 4 );
Output:-a, a, a, a
Here we can see the binary value of 7 is 111
SELECT EXPORT_SET ( 9, ‘a’, ‘b’, ‘,’, 4 );
Output:- a, b, b, a
Here We can see the binary value of 9 is 1001
SELECT EXPORT_SET ( 5, ‘a’, ‘b’, ‘,’, 4 );
Output:- a, b, a, a
Here we can see the binary value of 5 is 101
Read Also:-
Read more detail about MySQL String function manual
