CODEWITHSUNDEEP
sqlsql-serversql-server-2008casting
Kaleb Anderson
Kaleb Anderson

Reputation: 188

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

I'm running SQL Server 2008 (otherwise I would use Try_Parse) and I need to cast a field as a number in cases where it is non-empty. If the field is empty, an empty string or NULL should be returned. I would prefer if the field was stored as a number in the database, but I have no control over that at this time.

Here is what I have tried:

SELECT CASE WHEN AccountNumber='' THEN '' ELSE CAST(AccountNumber AS bigint) END AS AccountNumber FROM Accounts

OR

SELECT CASE WHEN CAST(AccountNumber AS bigint)=0 THEN '' ELSE CAST(AccountNumber AS bigint) END AS AccountNumber FROM Accounts

But both of these bring back 0 for empty account numbers. I feel that this should be possible but I am going crazy trying to figure it out! Thanks!

Upvotes: 2

Views: 2446

Answers (2)

Balde
Balde

Reputation: 590

You can use ISNUMERIC function:

SELECT CASE WHEN ISNUMERIC(AccountNumber) = 1 THEN CAST(AccountNumber AS BIGINT) ELSE NULL END
FROM Accounts

Upvotes: 1

mellamokb
mellamokb

Reputation: 56769

You can't select both numbers and empty strings into the same column, because they are incompatible types. That's why the empty strings get converted automatically to 0. NULL should work, however.

SELECT CASE WHEN AccountNumber='' OR AccountNumber IS NULL THEN NULL
  ELSE CAST(AccountNumber AS bigint) END AS AccountNumber
FROM Accounts

Upvotes: 6

Related Questions