Display numeric as empty string based on condition

Question

Display numeric as empty string based on condition

aaloneftis

SSC-Addicted

Points: 443
More actions
February 17, 2016 at 9:19 am

#311444

Hi all
I want to form a SELECT statement which selects a numeric column and returns an empty string when the numeric column has a value of -999. if not it should return the numeric value.
Can anyone help?
Thanks.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You can use a CASE statement for that. Remember that a column can only have one type, so if you want to return an empty string, you need to return all values as strings.

You could also use the NULLIF function, but it would return a NULL value instead of an empty string. However, you can format that in the front end.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61142 More actions · Answer 2

Luis beat me to it. Note the code/comments below.

DECLARE @table TABLE (NumericColumn int);

INSERT @table VALUES (1),(2),(-999);

-- This WONT work because blanks on numeric columns are returned as 0's

SELECT NumericColumn = CASE WHEN NumericColumn <> -999 THEN NumericColumn ELSE '' END

FROM @table;

-- NULLIF is your best best

SELECT NumericColumn = NULLIF(NumericColumn,-999)

FROM @table;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

aaloneftis SSC-Addicted Points: 443 More actions · Answer 3

Thanks to both answers. I am writing front end select only and not that experienced.

So the line below worked but I forgot to say that I need 2 values excluded:+999 and -999.

How do I rework the below for that?

ISNULL(CAST(NULLIF(CashFlowCoverage,-999) AS VARCHAR(100)),'')

Thanks

Alan Burstein SSC Guru Points: 61142 More actions · Answer 4

Here's two options:

DECLARE @table TABLE (CashFlowCoverage int);

INSERT @table VALUES (1),(2),(-999),(999);

SELECT CashFlowCoverage = NULLIF(NULLIF(CashFlowCoverage,-999),999)

FROM @table;

SELECT CashFlowCoverage = NULLIF(ABS(CashFlowCoverage),999)

FROM @table;

EDIT: changed the column name

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

aaloneftis SSC-Addicted Points: 443 More actions · Answer 5

aaloneftis

SSC-Addicted

Points: 443

February 17, 2016 at 9:59 am

#1859309

Thank you Alan. Appreciate your contribution.:-)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

You might want to reduce your varchar length. I've never heard of a common use for such large numbers.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61142 More actions · Answer 7

Luis Cazares (2/17/2016)
You might want to reduce your varchar length. I've never heard of a common use for such large numbers.

haha - I missed the comment about converting to varchar(100). Varchar(19) is enough to cover the largest bigint (9223372036854775807).

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Alan.B (2/17/2016)
Luis Cazares (2/17/2016)
You might want to reduce your varchar length. I've never heard of a common use for such large numbers.
haha - I missed the comment about converting to varchar(100). Varchar(19) is enough to cover the largest bigint (9223372036854775807).

Or varchar(20) for the smallest -9,223,372,036,854,775,808.

However, 10 should be enough for most systems and 5 might work for most catalogs.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2