Using IF condition inside CONCAT function of SQL Query

Question

I am trying to implement a concatenation inside a SQL query. I need to use comma as separator between the values.

select concat(technology,',',secondary_technology,',',tertiary_technology) as Technology from deals

This works completely fine. But if there are no values in secondary and tertiary technology columns, the result looks something like

Blue Prism,,

So I need to put a condition if secondary and tertiary technologies are null, then the commas need to be omitted. I am using the following query:

select concat(technology,if(secondary_technology is null,'',','),secondary_technology,if(tertiary_technology is null,'',','),tertiary_technology) as Technology from deals

But this throws error saying Incorrect Syntax near the keyword 'if'. Incorrect Syntax near ','.

Please help me with this! I am using MS SQL Server 2014 Thank you in advance..

score 3 · Accepted Answer · edited Jun 27 '18 at 15:30

3

Concat will ignore NULL values when appending. Try this

select CONCAT(technology, ',' +secondary_technology, ',' +tertiary_technology)
from deals

If technology column could be null.

select case when technology is null then stuff(Result, 1, 1, '') else Result end
from (
select technology, CONCAT(technology, ',' +secondary_technology, ',' +tertiary_technology) as Result
from deals
) tab

You might also want to check for Empty string columns using NULLIF.

select case 
    when coalesce(technology,'') = '' then stuff(Result, 1, 1, '') 
    else Result 
  end Result
from 
(
  select technology
  , CONCAT(technology, ',' + nullif(secondary_technology,''), ',' + nullif(tertiary_technology,'')) as Result
  from deals
) tab

edited Jun 27 '18 at 15:30

JohnLBevan

22,735
13
96
178

answered Jun 27 '18 at 14:09

Pratheek

71
5

Simplest solution. – EzLo Jun 27 '18 at 14:11
Thanks for the solution.. but if only secondary technology column has value while other two are empty, then there would be two commas before and after the secondary technology column value, right? How to solve that? – Saswat Rath Jun 27 '18 at 14:24
@SaswatRath if by no value you mean blank, that is an issue; if null, it's handled already (since `',' + null` = `null`). To treat blanks as nulls you can use `nullif`: `CONCAT(technology, ',' +nullif(secondary_technology,''), ',' +nullif(tertiary_technology,'')) ` – JohnLBevan Jun 27 '18 at 15:18
1

@JohnLBevan Your solution solves my problem.. This is exactly what i Was looking for.. Thank you so much for the help!! :) – Saswat Rath Jun 28 '18 at 06:53

Yogesh Sharma · Answer 2 · 2018-06-27T14:27:28.960

1

Use coalesce() :

select concat(technology, coalesce(',' +secondary_technology, ''), coalesce(',' +tertiary_technology, '')) as Technology    
from deals;

EDIT : Use stuff() :

select stuff(<concat query> 1,1, '')
from deals;

edited Jun 27 '18 at 14:27

answered Jun 27 '18 at 13:44

Yogesh Sharma

49,870
5
26
52

Thanks for the help.. This is working fine where only technology column has value and no values in secondary and tertiary column. But if both technology and secondary technology columns have values, then an extra comma appears after the value after the secondary technology value.. – Saswat Rath Jun 27 '18 at 14:12
Also if there's no value in technology column but values in other two columns, then a comma appears at the beginning of the Technology column. Please solve this too.. – Saswat Rath Jun 27 '18 at 14:16

score 0 · Answer 3 · answered Jun 27 '18 at 14:01

0

Please use the below code as alternative:

SELECT CONCAT(technology, ISNULL(secondary_technology,''),ISNULL(tertiary_technology,'')) AS Technology
FROM deals;

answered Jun 27 '18 at 14:01

user1469712

54
4

score 0 · Answer 4 · answered Jun 27 '18 at 14:46

try this:

select concat(
  technology, 
  CASE WHEN secondary_technology IS NOT NULL
      THEN concat(', ', secondary_technology)
      ELSE ''
  END,
  CASE WHEN tertiary_technology IS NOT NULL
      THEN concat(', ', tertiary_technology)
      ELSE ''
  END,
  ) as Technology    
from deals;

See this answer related to this issue: https://stackoverflow.com/a/19242830/3554534

Using IF condition inside CONCAT function of SQL Query

4 Answers4