sql server - SQL: If field is empty, return empty string, otherwise cast as number -


i'm running sql server 2008 (otherwise use try_parse) , need cast field number in cases non-empty. if field empty, empty string or null should returned. prefer if field stored number in database, have no control on @ time.

here have tried:

select case when accountnumber='' '' else cast(accountnumber bigint) end accountnumber accounts

or

select case when cast(accountnumber bigint)=0 '' else cast(accountnumber bigint) end accountnumber accounts

but both of these bring 0 empty account numbers. feel should possible going crazy trying figure out! thanks!

you can't select both numbers , empty strings same column, because incompatible types. that's why empty strings converted automatically 0. null should work, however.

select case when accountnumber='' or accountnumber null null   else cast(accountnumber bigint) end accountnumber accounts 

Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -