How to substitute NULL with empty string in sql 2017

Question

Column occurrence_limit is money datatype,

If it is NULL I want just empty string (''), but it gives me 0.00.

ISNULL(occurrence_limit,'')  as occurrence_limit

How can I simply have '' instead of 0.00?

The empty string is not a valid value for money. Why not just use null? — GMB
– GMB, Commented Mar 5, 2020 at 18:16
Don't. Simple as that. Handle it in your presentation layer. '' is not a number, so it can't be stored as a money value, and it's implicitly converted to 0. You most certainly don't want to treat your numbers as a varchar (as I can assure you that 100 is greater than 2, not less than it) so leave it as it is. — Thom A
– Thom A ♦, Commented Mar 5, 2020 at 18:16
The outcome goes to excel sheet. User requirements is to have blank if NULL — Serdia
– Serdia, Commented Mar 5, 2020 at 18:17
So fix what ever is outputting to Excel, @Serdia . Most applications don't actually display NULL they show an empty "cell", so what ever ETL process you are using is actually putting "NULL". Fix the ETL process. It's only really IDEs that tend to display NULL, as it is different to '' when dealing with strings. — Thom A
– Thom A ♦, Commented Mar 5, 2020 at 18:18

Eric · Accepted Answer · 2020-03-05 18:25:28Z

2

As others have said, don't do it. But if you need to:

SELECT ISNULL(CONVERT(VARCHAR(9), occurrence_limit), '') AS occurrence_limit

answered Mar 5, 2020 at 18:25

Eric

7621 gold badge6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thom A Over a year ago

Just to give reason why it's bad: it's that the values aren't numerical anymore. For important things, like money, that is a problem. '100' is less than '2', for example, and '17' + '2' is '172'.

Julian · Accepted Answer · 2020-03-05 23:12:46Z

0

DROP table #testStak create table #testStak(nombre varchar(50),moneycant money)

INSERT INTO #testStak VALUES ('ALFA',10) 
INSERT INTO #testStak VALUES ('BETA',NULL) 
INSERT INTO #testStak VALUES ('GAMMA',5000)

SELECT nombre,ISNULL(CAST(moneycant AS varchar),'') FROM #testStak

edited Mar 5, 2020 at 23:12

Julian

37.3k24 gold badges138 silver badges196 bronze badges

answered Mar 5, 2020 at 23:06

user13015606

2 Comments

SMor Over a year ago

Not related to question

user13015606 Over a year ago

actually it does, its a form to put an empty space instead of 0.00 in a column of money.

Nayanish Damania · Accepted Answer · 2020-03-06 07:31:24Z

0

Try this out:

SELECT ISNULL(NULLIF(occurrence_limit, 0),'')  as occurrence_limit

answered Mar 6, 2020 at 7:31

Nayanish Damania

6526 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to substitute NULL with empty string in sql 2017

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related