sql server replace blank with null in select

Hi,

Does anyone know a way to replace '' (blank) with NULL in the select statement without using a case statement?

i.e.
convert this to a select statement using replace, stuff, or?

SELECT CASE AccountId
                       WHEN ''
                           THEN NULL
                       ELSE AccountId
             END  AS AccountId
  FROM Accounts


 
ssebringAsked:
Who is Participating?
 
YiogiConnect With a Mentor Commented:
This would be much more efficient:

SELECT NullIf(AccountId, '') FROM Accounts
0
 
HuyBDCommented:
try this
SELECT CASE WHEN AccountId=''
       THEN NULL
       ELSE AccountId
       END  AS AccountId
  FROM Accounts

Open in new window

0
 
Lee SavidgeCommented:
Hi,

select isnull(mycolumn, '') from mytable

Regards,

Lee
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
YiogiCommented:
Lee he wants to do the exact oposite of what you posted :)
0
 
Lee SavidgeCommented:
Hi,

Sorry, misread the question there. Try this:

select replace(mycolumn, '', null) from mytable

Regards,

Lee
0
 
HuyBDCommented:
Yiogi, Lee just convert null to '', not '' to null value
Lee, it return null even value of field differ from '' (e.g 'a b')
0
 
ssebringAuthor Commented:
I forgot that NullIf was available!  It's been a long time since I needed it.  Thanks!
0
 
YiogiCommented:
Guys why don't you just use what I posted? It will work lol.
0
 
Lee SavidgeCommented:
Hey,

You learn something everyday. Been doing SQL since about 1999 and never came across the NullIf command!

Thanks for the tip :)

Best regards,

Lee < Old dog has been taught a new trick :)
0
All Courses

From novice to tech pro — start learning today.