Convert NULL to blank or if not NULL, Remove Commas from string

[cbjones]

I am trying to create a query such that when pulling data from a table, then

If the result is "NULL", replace "NULL" in the column with a "blank"...OR... if the result is a string (not NULL), strip all commas from the resulting string and show the result in the column (Example: showing "ABC, Inc." as "ABC Inc.")...AND... label the column with a heading.

So far, I have totally failed in attempts to write this line of my query...

Many Thanks!
Frustrated Newbie

[Jim Doherty]

@cbjones
Look at case statement useage typically

SELECT YourColumnName,CASE WHEN ColumnName Is Null THEN 'blank' ELSE replace(YourColumnName,',','') END AS MyColumn from dbo.YourTableName

I know you did not want specifically the word 'blank' but you get the idea just remove and leave single quotes instead

I know some query designers GUI interfaces dont support case statement useage Access ADP project files for one, but then you just create the view on the server and call the view. Case statement is flexible as you can see you can add many arguments to it.

[cbjones]

Many thanks...here's what I did:

SELECT MailAddress2,CASE WHEN MailAddress2 Is Null THEN '' ELSE replace(MailAddress2,',',' ') END AS [Insured Address 2] from Insured

and I got two columns. What am I doing wrong?

MailAddress2 Insured Address 2
Suite C10 Suite C10
NULL
NULL
NULL
NULL
NULL
NULL
NULL
1020 Hidden Harbour Dr. C-1 1020 Hidden Harbour Dr. C-1
NULL
NULL

[Jim Doherty]

@cbjones
You are doing nothing wrong. You asked for two columns of data in your SELECT portion one being the data column and the other being the derived from a calculation Case statement. If you dont wantt the first column just remove it

SELECT CASE WHEN MailAddress2 Is Null THEN '' ELSE replace(MailAddress2,',',' ') END AS [Insured Address 2] from Insured

[cbjones]

@Jim Doherty
Manually remove it? I can do that if need be. Thanks Jim.

[Jim Doherty]

Just manually change the SQL to match my last post. Your MailAddress2 data will remain in your table as it should do of course. The SQL Case statement is merely testing that data and returning a calculated column ONLY if you understand me

[cbjones]

@Jim Doherty
EUREKA! Thanks for the ideas! Here is the winner in the clubhouse; it first determines if the content is NULL and, if so, suppresses displaying NULL, and if not, removes the commas from the existing string, if any, and replaces the commas with a "-" {dash}:

(CASE WHEN I.MailAddress2 is NULL THEN IsNull(I.MailAddress2, '') ELSE REPLACE(I.MailAddress2, ',','-') END) as [Insured Address 2]

Thank you thank you thank you!

[Jim Doherty]

@cbjones
Hahahah so pleased you conquered it. Well done! :)