Geek Answers Handbook

For null specific sql search time fields

select isnull(column1,'')+','+isnull(column2,'')+','+isnull(column3,'') AS column4 from table

From the above query, I get what I need, which is really good. But the fact is that if all the columns are all NULL, I get the commas that I used to separate the fields.

I want the comma to be replaced by NULLwhen every field NULL. Can someone help me with this? thanks!

+4
source share
4 answers

You can pack + ','inISNULL()

select isnull(column1+',','')+isnull(column2+',','')+isnull(column3,'') AS column4 from table
+3
source

You can do this using the stuff()following:

select stuff((coalesce(',' + col1, '') + 
              coalesce(',' + col2, '') +
              coalesce(',' + col3, '')
             ), 1, 1, '')

Other databases often have a function named concat_ws()that also does this.

+3

CASE EXPRESSION:

SELECT CASE WHEN column1 is not null then column1 + ',' else '' end +
       CASE WHEN column2 is not null then column2 + ',' else '' end +
       CASE WHEN column3 is not null then column3 else '' end as column4
+2

First, count the number of spaces in the name and add it to the counter, and you will get the result. You can also trim the string if necessary

declare @count as int
declare @spaceCount as int

select @count =(LEN('amjad habib'))
select @spaceCount =@count-LEN(REPLACE('amjad habib', ' ', ''))

SELECT SUBSTRING('amjad habib',1 , 8 + @spaceCount )
+2
source

More articles:


All Articles