Loading

MySQL String Functions Part-2

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 out put 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 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(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 out put 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

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w