How to Concatenate Multiple columns in MySQL

In this tutorial, I show How you can concatenate multiple columns in MySQL.

You can simply do this programmatically by separately select fields from MySQL Table and store their values in the single variable after concat their values.

But you can make the above process little simpler by concatenating the values while selecting rows from DataBase Table.

Let’s take a simple example –

You have two columns – firstname, lastname within your DataBase Table you want to show both the columns values in a single string form. In case you can MySQL functions to combine the values of the columns.

There are two functions for doing this –

• CONCAT
• CONCAT_WS

Both functions work similar but have little difference.

---

Contents

1. CONCAT
2. CONCAT_WS
3. Using them in WHERE CLAUSE
4. Conclusion

---

1. CONCAT

This function is used to concatenate multiple columns or strings into a single one. Arguments are separated by a comma.

Syntax – 

CONCAT( column1, column2, ... )

OR

CONCAT ( string1, string2, ... )

For demonstration, I am using Users Table which has following records.

id	username	firstname	lastname
1	yssyogesh	Yogesh	Singh
2	sonarika	Sonarika	Bhadoria
3	vishal	Vishal	Sahu

Example

I am using this function to concatenate firstname, lastname columns and set it ALIAS to fullname.

SELECT 
username, 
CONCAT( firstname, " ", lastname ) AS fullname 
FROM users

Output

id	username	fullname
1	yssyogesh	Yogesh Singh
2	sonarika	Sonarika Bhadoria
3	vishal	Vishal Sahu

---

2. CONCAT_WS

The CONCAT_WS() function not only add multiple string values and makes them a single string value. It also let you define separator ( ” “, ” ,  “, ” – “,” _ “, etc.).

Syntax – 

CONCAT_WS( SEPERATOR, column1, column2, ... )

OR

CONCAT ( SEPERATOR, string1, string2, ... )

Example 

SELECT 
username, 
CONCAT_WS( " ", firstname, lastname ) AS fullname 
FROM users

Output

id	username	fullname
1	yssyogesh	Yogesh Singh
2	sonarika	Sonarika Bhadoria
3	vishal	Vishal Sahu

As I said at the start of the function you can define any other characters instead of space.

---

3. Using them in WHERE CLAUSE

You can use both of them in WHERE CLAUSE for selection based on condition.

Example

SELECT * 
FROM users 
WHERE CONCAT_WS(" ",firstname,lastname) = "Sonarika Bhadoria"

Output

id	username	firstname	lastname
2	sonarika	Sonarika	Bhadoria

Same you can do with CONCAT function.

---

4. Conclusion

This functions generally you can use when you have to show multiple columns values within the single string. You can specify your own separator values like – space, comma, dash, etc in the function.

This saves you some of the time on the programming side where you add the column values separately.

How The SUBSTR() Function Works in MySQL

In MySQL, the SUBSTR() function returns a substring starting from the specified position.

Both SUBSTR() and MID() are synonyms of SUBSTRING().

Syntax

The basic syntax goes like this:

SUBSTR(str,pos,len)

Here, str is the string, pos is the position to start the substring from, and len is an optional argument that determines the number of characters to return from that starting position.

There are several variations on how you can use this function, so the full range of syntaxes looks like this:

SUBSTR(str,pos)
SUBSTR(str FROM pos)
SUBSTR(str,pos,len)
SUBSTR(str FROM pos FOR len)

These are demonstrated in the following examples.

Example 1 – Basic Usage

Here’s an example of the SUBSTR(str,pos) syntax:

SELECT SUBSTR('I play the drums', 3) Result;

Result:

+----------------+
| Result         |
+----------------+
| play the drums |
+----------------+

In this example, I take a substring from the string, starting at position 3.

Example 2 – Using the FROM Clause

Here’s how to do the same thing, but this time using the SUBSTR(str FROM pos) syntax:

SELECT SUBSTR('I play the drums' FROM 3) Result;

Result:

+----------------+
| Result         |
+----------------+
| play the drums |
+----------------+

So we get the same result.

In this case, FROM is standard SQL. Note that this syntax doesn’t use commas.

Example 3 – Specify a Length

In this example, I use the SUBSTR(str,pos,len) syntax:

SELECT SUBSTR('I play the drums', 3, 4) Result;

Result:

+--------+
| Result |
+--------+
| play   |
+--------+

Here I specify that the returned substring should be 4 characters long.

Example 4 – Specify a Length (using the FOR Clause)

In this example, I use the SUBSTR(str FROM pos FOR len) syntax:

SELECT SUBSTR('I play the drums' FROM 3 FOR 5) Result;

Result:

+--------+
| Result |
+--------+
| play   |
+--------+

So this time we used standard SQL to achieve the same result

MySQL SOUNDEX() Examples

One of the many MySQL string functions is the SOUNDEX() function. This function returns a Soundex string from a given string. If two words sound the same, they should have the same Soundex string. If two words sound similar, but not exactly the same, their Soundex string might look similar but not exactly the same.

This article contains a bunch of Soundex examples to demonstrate how the SOUNDEX()function works in MySQL.

Syntax

First, let’s look at the syntax:

SOUNDEX(str)

Where str is the string to which you require the Soundex string.

Example

Here’s an example of retrieving the Soundex string from a string:

SELECT SOUNDEX('Sure');

Result:

+-----------------+
| SOUNDEX('Sure') |
+-----------------+
| S600            |
+-----------------+

So in this case, the word Sure has a Soundex string of S600.

Example – Exact Match

Here’s an example of where two words sound the same (or very similar), and therefore, they share the same Soundex string:

SELECT 
    SOUNDEX('Sure') AS Sure, 
    SOUNDEX('Shore') AS Shore;

Result:

+------+-------+
| Sure | Shore |
+------+-------+
| S600 | S600  |
+------+-------+

Here are some more exact match examples:

SELECT 
    SOUNDEX('Dam') AS Dam,
    SOUNDEX('Damn') AS Damn,
    SOUNDEX('Too') AS Too,
    SOUNDEX('Two') AS Two;

Result:

+------+------+------+------+
| Dam  | Damn | Too  | Two  |
+------+------+------+------+
| D500 | D500 | T000 | T000 |
+------+------+------+------+

Example – Non Match

Here’s an example of where two words don’t sound the same, and therefore, they have different Soundex strings:

SELECT 
    SOUNDEX('Water') AS Water, 
    SOUNDEX('Coffee') AS Coffee;

Result:

+-------+--------+
| Water | Coffee |
+-------+--------+
| W360  | C100   |
+-------+--------+

As you can see, the Soundex string is completely different for these two words.

Example – Different Spellings

Here’s an example of two words that have different spellings (depending on which country you’re from):

SELECT 
  SOUNDEX('Color') AS 'Color',
  SOUNDEX('Colour') AS 'Colour';

Result:

+-------+--------+
| Color | Colour |
+-------+--------+
| C460  | C460   |
+-------+--------+

So we can see that such words will share the same Soundex string (as long as they’re pronounced the same way).

Example – Same Sound, Different Soundex

There are cases where words sound the same, but they have different Soundex strings. The most common reason for this is that they start with a different letter, one of which is a silent letter. You might’ve noticed from the previous examples that the Soundex string starts with the first letter of the string.

Therefore, if you have two words that are pronounced exactly the same, but they start with a different letter, they’ll have a different Soundex string.

Here are some examples:

SELECT
    SOUNDEX('Hole') AS 'Hole',
    SOUNDEX('Whole') AS 'Whole',
    SOUNDEX('Our') AS Our,
    SOUNDEX('Hour') AS Hour;

Result:

+------+-------+------+------+
| Hole | Whole | Our  | Hour |
+------+-------+------+------+
| H400 | W400  | O600 | H600 |
+------+-------+------+------+

The pairs in this example have different Soundex strings solely because their first letter is different.

Example – Soundex in a Database Query

Here’s an example of using SOUNDEX() in a database query. In this case, we’re looking for any records that sound like “Ay See Dee Ci”:

SELECT ArtistName 
FROM Artists
WHERE SOUNDEX(ArtistName) = SOUNDEX('Ay See Dee Ci');

Result:

+------------+
| ArtistName |
+------------+
| AC/DC      |
+------------+

So AC/DC apparently has the same Soundex code as Ay See Dee Ci (at least when using MySQL) Just to be sure, here’s the Soundex codes for both of those strings:

SELECT 
  SOUNDEX('AC/DC') AS 'AC/DC',
  SOUNDEX('Ay See Dee Ci') AS 'Ay See Dee Ci';

Result:

+-------+---------------+
| AC/DC | Ay See Dee Ci |
+-------+---------------+
| A232  | A232          |
+-------+---------------+

An Alternative: SOUNDS LIKE

An alternative query could have been constructed using SOUNDS LIKE instead of the SOUNDEX() function. Like this:

SELECT ArtistName FROM Artists
WHERE ArtistName SOUNDS LIKE 'Ay See Dee Ci';

Result:

+------------+
| ArtistName |
+------------+
| AC/DC      |
+------------+

CONV() – Convert Numbers Between Different Bases in MySQL

When working with MySQL, you can use the CONV() function to convert a number from one base to another. It takes three arguments; the number to convert, the original base, and the base you want to convert it to.

Syntax

Here’s the official syntax:

CONV(N,from_base,to_base)

Where from_base is the original base, and to_base is the base you want to convert the number into.

Example – Decimal to Binary

Here’s an example of converting a number from base 10 (decimal) to base 2 (binary):

SELECT CONV(3, 10, 2);

Result:

+----------------+
| CONV(3, 10, 2) |
+----------------+
| 11             |
+----------------+

So we can see that 3 in decimal converts to 11 in binary.

In the case of binary, MySQL also has a BIN() function that allows you to convert from decimal to binary. Therefore, the above example is the equivalent of the following:

SELECT BIN(3);

Result:

+--------+
| BIN(3) |
+--------+
| 11     |
+--------+

Example – Binary to Decimal

However, one benefit of the CONV() function is that it also enables you to convert back the other way. So we could switch the above example to convert from binary to decimal:

SELECT CONV(11, 2, 10);

Result:

+-----------------+
| CONV(11, 2, 10) |
+-----------------+
| 3               |
+-----------------+

Example – Decimal to Hexadecimal

In this example we convert from decimal to hexadecimal:

SELECT CONV(13, 10, 16);

Result:

+------------------+
| CONV(13, 10, 16) |
+------------------+
| D                |
+------------------+

As demonstrated here, 13 in base 10 (decimal) converts into D in base 16 (hexadecimal).

Here’s another example, this time using a bigger number:

SELECT CONV(12734, 10, 16);

Result:

+---------------------+
| CONV(12734, 10, 16) |
+---------------------+
| 31BE                |
+---------------------+

Similar to the BIN() function for binary conversions, MySQL also has a HEX() function that converts a number from decimal to hexadecimal. So the previous example could be rewritten as follows:

SELECT HEX(12734);

Result:

+------------+
| HEX(12734) |
+------------+
| 31BE       |
+------------+

Example – Base 36

The CONV() function accepts a minimum base of 2 (binary) and a maximum base of 36. Here’s an example of converting from base 10 to base 36:

SELECT CONV(12734, 10, 36);

Result:

+---------------------+
| CONV(12734, 10, 36) |
+---------------------+
| 9TQ                 |
+---------------------+

How the WEIGHT_STRING() Function Works in MySQL

In MySQL, the WEIGHT_STRING() function returns the weight string for the input string. The return value is a binary string that represents the comparison and sorting value of the string.

If the input string is a nonbinary value, the return value contains the collation weights for the string. If it’s a binary value, the result is the same as the input string. This is because the weight for each byte in a binary string is the byte value.

This function is a debugging function intended for internal use. It can be used for testing and debugging of collations. Note that its behavior can change between MySQL versions.

Syntax

The syntax goes like this:

WEIGHT_STRING(str [AS {CHAR|BINARY}(N)] [flags])

Where str is the input string. The optional AS clause allows you to cast the input string to a given type and length. The optional flags argument is not currently used in MySQL (as of version 8.0).

Example 1 – Basic Usage

Here’s a basic example of usage using a nonbinary input string:

SELECT HEX(WEIGHT_STRING('Cat'));

Result:

+---------------------------+
| HEX(WEIGHT_STRING('Cat')) |
+---------------------------+
| 1C7A1C471E95              |
+---------------------------+

Note that I use the HEX() function to display the WEIGHT_STRING() result. This is because WEIGHT_STRING() returns a binary result. We can use HEX() to display the result in a printable form.

If I don’t use HEX() in this example I get this:

SELECT WEIGHT_STRING('Cat');

Result:

+----------------------+
| WEIGHT_STRING('Cat') |
+----------------------+
| zG?                |
+----------------------+

So just to be clear, here’s the string, the hexadecimal representation of that string, and the hexadecimal representation of its weight string:

SET @str = 'Cat';
SELECT @str, HEX(@str), HEX(WEIGHT_STRING(@str));

Result:

+------+-----------+--------------------------+
| @str | HEX(@str) | HEX(WEIGHT_STRING(@str)) |
+------+-----------+--------------------------+
| Cat  | 436174    | 1C7A1C471E95             |
+------+-----------+--------------------------+

Example 2 – The AS Clause

Here’s an example using the AS clause to cast the input string to a given type and length.

SET @str = 'Cat';
SELECT 
  HEX(WEIGHT_STRING(@str AS CHAR(3))) 'Char 3',
  HEX(WEIGHT_STRING(@str AS CHAR(8))) 'Char 8',
  HEX(WEIGHT_STRING(@str AS BINARY(3))) 'Binary 3',
  HEX(WEIGHT_STRING(@str AS BINARY(8))) 'Binary 8';

Result:

+--------------+--------------+----------+------------------+
| Char 3       | Char 8       | Binary 3 | Binary 8         |
+--------------+--------------+----------+------------------+
| 1C7A1C471E95 | 1C7A1C471E95 | 436174   | 4361740000000000 |
+--------------+--------------+----------+------------------+

Example 3 – Collation

The following two examples demonstrate how a string can have a different weight string, depending on the collation being used. The collation used in the first example is accent-insensitive and case-insensitive. The collation used in the second example is accent-sensitive and case-sensitive.

SET @upper = _utf8mb4 'CAT' COLLATE utf8mb4_0900_ai_ci;
SET @lower = lcase(@upper);
SELECT 
  @upper 'String', 
  HEX(@upper) 'Hex', 
  HEX(WEIGHT_STRING(@upper)) 'Weight String'
UNION ALL
SELECT 
  @lower, 
  HEX(@lower), 
  HEX(WEIGHT_STRING(@lower));

Result:

+--------+--------+---------------+
| String | Hex    | Weight String |
+--------+--------+---------------+
| CAT    | 434154 | 1C7A1C471E95  |
| cat    | 636174 | 1C7A1C471E95  |
+--------+--------+---------------+

And here’s the same example, except with an accent-sensitive and case-sensitive collation.

SET @upper = _utf8mb4 'CAT' COLLATE utf8mb4_0900_as_cs;
SET @lower = lcase(@upper);
SELECT 
  @upper 'String', 
  HEX(@upper) 'Hex', 
  HEX(WEIGHT_STRING(@upper)) 'Weight String'
UNION ALL
SELECT 
  @lower, 
  HEX(@lower), 
  HEX(WEIGHT_STRING(@lower));

Result:

+--------+--------+----------------------------------------------+
| String | Hex    | Weight String                                |
+--------+--------+----------------------------------------------+
| CAT    | 434154 | 1C7A1C471E9500000020002000200000000800080008 |
| cat    | 636174 | 1C7A1C471E9500000020002000200000000200020002 |
+--------+--------+----------------------------------------------+

How the UPPER() Function Works in MySQL

In MySQL, the UPPER() function converts lowercase characters to uppercase, according to the current character set mapping (the default mapping is utf8mb4).

Syntax

The syntax goes like this:

UPPER(str)

Where str is the string to be changed to uppercase.

Example

Here’s an example:

SELECT UPPER('cat');

Result:

+--------------+
| UPPER('cat') |
+--------------+
| CAT          |
+--------------+

As you might expect, if the string already contains any uppercase characters, those characters will remain in uppercase.

Example:

SELECT UPPER('Cat');

Result:

+--------------+
| UPPER('Cat') |
+--------------+
| CAT          |
+--------------+

Database Example

Here’s an example of selecting data from a database and converting it to uppercase:

USE Music;
SELECT 
    ArtistName AS Original, 
    UPPER(ArtistName) AS Uppercase
FROM Artists
LIMIT 5;

Result:

+------------------+------------------+
| Original         | Uppercase        |
+------------------+------------------+
| Iron Maiden      | IRON MAIDEN      |
| AC/DC            | AC/DC            |
| Allan Holdsworth | ALLAN HOLDSWORTH |
| Buddy Rich       | BUDDY RICH       |
| Devin Townsend   | DEVIN TOWNSEND   |
+------------------+------------------+

Binary Strings

This function doesn’t work on binary strings. If you need to use it on a binary string, you’ll need to convert it to a nonbinary string first. Here’s an example:

SET @str = BINARY 'Cat';
SELECT 
  UPPER(@str) AS 'Binary', 
  UPPER(CONVERT(@str USING utf8mb4)) AS 'Nonbinary';

Result:

+--------+-----------+
| Binary | Nonbinary |
+--------+-----------+
| Cat    | CAT       |
+--------+-----------+

The UCASE() Function

The UCASE() function is a synonym for UPPER(). Note that if you use UCASE() within a view, it will be rewritten and stored as UPPER().

Convert to Lowercase

The LOWER() and LCASE() functions work the same way to convert characters to lowercase.

How the UNHEX() Function Works in MySQL

In MySQL, the UNHEX() function allows you to “unhex” a string in MySQL. In other words, it allows you to convert a hexadecimal value to a human-readable string.

Specifically, the function interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.

Syntax

The syntax goes like this:

UNHEX(str)

Where str is the string to unhex.

Example 1 – Basic Usage

Here’s a basic example:

SELECT UNHEX('436F636F6E75742070616C6D') AS Unhexed;

Result:

+--------------+
| Unhexed      |
+--------------+
| Coconut palm |
+--------------+

So I provided a hex value as an argument, and the function unhexed that value and returned the result (which is a binary string).

Example 2 – Convert a String to Hexadecimal

We can also do the reverse by using the HEX() function to convert the string into a hexadecimal value:

SELECT HEX('Coconut palm') AS Hexed;

Result:

+--------------------------+
| Hexed                    |
+--------------------------+
| 436F636F6E75742070616C6D |
+--------------------------+

Therefore, we could nest one function within the other, and we’d simply get our argument returned:

SELECT 
  UNHEX(HEX('Coconut palm')) AS 'Result 1',
  HEX(UNHEX('436F636F6E75742070616C6D')) AS 'Result 2';

Result:

+--------------+--------------------------+
| Result 1     | Result 2                 |
+--------------+--------------------------+
| Coconut palm | 436F636F6E75742070616C6D |
+--------------+--------------------------+

Example 3 – Invalid Hexadecimal Characters

The argument must contain valid hexadecimal characters. If any of the characters are not valid hex characters, the result will be NULL:

SELECT 
  UNHEX(' '),
  UNHEX('_xyz');

Result:

+------------+---------------+
| UNHEX(' ') | UNHEX('_xyz') |
+------------+---------------+
| NULL       | NULL          |
+------------+---------------+

Example 4 – Numeric Arguments

The UNHEX() function doesn’t work on the hexadecimal equivalents of numeric values.

Here’s an example of what I mean:

SELECT 
  HEX(1234),
  UNHEX('4D2');

Result:

+-----------+--------------+
| HEX(1234) | UNHEX('4D2') |
+-----------+--------------+
| 4D2       | ?            |
+-----------+--------------+

Another way to demonstrate this would be:

SELECT UNHEX(HEX(1234));

Result:

+------------------+
| UNHEX(HEX(1234)) |
+------------------+
| ?                |
+------------------+

So what this shows us is that the hexadecimal equivalent of 1234 is 4D2, and the UNHEX() function can’t handle that value.

In such cases, you can use the CONV() function instead:

SELECT 
  HEX(1234),
  CONV('4D2', 16, 10),
  CONV(HEX(1234), 16, 10);

Result:

+-----------+---------------------+-------------------------+
| HEX(1234) | CONV('4D2', 16, 10) | CONV(HEX(1234), 16, 10) |
+-----------+---------------------+-------------------------+
| 4D2       | 1234                | 1234                    |
+-----------+---------------------+-------------------------+

In this example, we use CONV() to convert the value from base-16 (hexadecimal) to base-10 (decimal).