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

**Jim Doherty** · May 19 '10, 12:40 AM

Originally posted by 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"...O R... 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

Look at case statement useage typically

Code:

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** · May 19 '10, 01:31 PM

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

SELECT MailAddress2,CA SE WHEN MailAddress2 Is Null THEN '' ELSE replace(MailAdd ress2,',',' ') 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** · May 19 '10, 02:31 PM

Originally posted by cbjones

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

SELECT MailAddress2,CA SE WHEN MailAddress2 Is Null THEN '' ELSE replace(MailAdd ress2,',',' ') 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

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

Code:

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

**cbjones** · May 19 '10, 11:25 PM

Originally posted by Jim Doherty

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

Code:

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

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

**Jim Doherty** · May 20 '10, 12:07 AM

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** · May 21 '10, 08:36 PM

Originally posted by Jim Doherty

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

Code:

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

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.MailAd dress2, '') ELSE REPLACE(I.MailA ddress2, ',','-') END) as [Insured Address 2]

Thank you thank you thank you!

**Jim Doherty** · May 21 '10, 09:41 PM

Originally posted by cbjones

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.MailAd dress2, '') ELSE REPLACE(I.MailA ddress2, ',','-') END) as [Insured Address 2]

Thank you thank you thank you!

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

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment