CODEWITHSUNDEEP
mysql
Dumb_Shock
Dumb_Shock

Reputation: 1008

Mysql string concatenation only when the value exists

I have a table

table1: ID, object1, object2, object3

I want to concatenate the object1, object2 and object3 only when the value exists in all the objects.

For example:

select ID, object1 +'\'+ Object2 +'\'+ object3 from table1

I want this query to be true only when values are present in all the objects.

The result from the above query when there is no value in object 2 and object 3 would be something like:

object1\\

But I want the output to be only as (when no value in object2 and object3)

object1 (without back slashes)

I want to avoid scenarios that arise from above query when there is no value present in object2 and object3. How do i write a query to dynamically concatenate based on the values availability?

Upvotes: 0

Views: 937

Answers (1)

Schwern
Schwern

Reputation: 165546

Use concat_ws. It will automatically skip nulls.

mysql> select concat_ws('\\', 'foo', 'bar', null, 'baz');
+--------------------------------------------+
| concat_ws('\\', 'foo', 'bar', null, 'baz') |
+--------------------------------------------+
| foo\bar\baz                                |
+--------------------------------------------+

However it will not skip blanks.

mysql> select concat_ws('\\', 'foo', 'bar', '', 'baz');
+------------------------------------------+
| concat_ws('\\', 'foo', 'bar', '', 'baz') |
+------------------------------------------+
| foo\bar\\baz                             |
+------------------------------------------+

A good schema will not treat nulls and '' the same. But sometimes you don't have a choice. In that case use nullif to turn blanks into nulls.

mysql> set @var = '';
mysql> select concat_ws('\\', 'foo', 'bar', nullif(@var, ''), 'baz');
+--------------------------------------------------------+
| concat_ws('\\', 'foo', 'bar', nullif(@var, ''), 'baz') |
+--------------------------------------------------------+
| foo\bar\baz                                            |
+--------------------------------------------------------+

And you might want to turn that all into a stored procedure.

Upvotes: 2

Related Questions