Converting varchar values to decimal while handling NULLs and EMPTY strings as 0

Question

I have a column of varchar datatype populated with a mix of values such as 1.2, 5.33 while also having NULLs and EMPTY strings. I want to convert all the values to decimal while treating NULLs and EMPTY strings as 0.

I can change the NULLs or EMPTY strings using the CONVERT function like below. Like this I replace the NULLs and EMPTY strings with a varhcar 0.

CASE WHEN Column1 = '' OR Column1= NULL THEN '0' ELSE Column1 END AS 'NewColumn1'

However what I want to do is to be able to then convert this data (output of NewColumn1) into decimal but when I place the CASE expression into a CONVERT or a CAST function I will have errors.

I also tried the following. CONVERT(DECIMAL(10,4), ISNULL(Column1, '0')) however it fails since here I am not handling the EMPTY strings.

Any ideas how can I solve this problem.

CAST((CASE WHEN Column1='' OR Column1 IS NULL THEN '0' ELSE Column1 END ) AS DECIMAL(18,2)) — Shakeer Mirza
– Shakeer Mirza, Commented Apr 4, 2017 at 7:41

Dale K · Accepted Answer · 2024-06-17 22:19:18Z

8

Simple way:

SELECT CONVERT(DECIMAL(10, 4), ISNULL(NULLIF(Column1, ''), '0'))

Your CASE expression doesn't work because you're checking if Column1 = NULL. You should check if it IS NULL.

CASE WHEN Column1 = '' OR Column1 IS NULL THEN '0' ELSE Column1 END AS 'NewColumn1'

edited Jun 17, 2024 at 22:19

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Apr 4, 2017 at 7:41

Marko Juvančič

5,9201 gold badge27 silver badges43 bronze badges

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

2 Comments

user2307236 Over a year ago

Thank you Marko. This first solution is working and also very clean.

Yahfoufi Over a year ago

@user2307236 if this answer solved your issue you have to accept it, it is not sufficient to say thanks. Also When having many well answered question without accepting good answers. Other user will not pay attention for your questions. good luck

Kushan · Accepted Answer · 2017-04-04 08:03:05Z

3

Try this,

SELECT CAST(ISNULL(NULLIF(Column1, ''),0) AS DECIMAL(12,2)) FROM table

edited Apr 4, 2017 at 8:03

answered Apr 4, 2017 at 7:48

Kushan

10.7k4 gold badges39 silver badges41 bronze badges

2 Comments

Marko Juvančič Over a year ago

This doesn't work as required - it doesn't work for empty string.

Kushan Over a year ago

I fixed that issue

Joel Coehoorn · Accepted Answer · 2023-12-26 02:44:58Z

0

Try this:

SELECT CAST(COALESCE(NULLIF(Column1, ''),'0') AS DECIMAL(14,5)) AS Column1 
FROM table

edited Dec 26, 2023 at 2:44

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

answered Dec 26, 2023 at 2:29

Sri Hari Adabala

111 bronze badge

Comments

Hari · Accepted Answer · 2024-12-08 14:42:02Z

0

We can first convert varchar or char into INT and then decimal. It will handle both null and empty string. Ex. CONVERT(Decimal(precision, scale), convert(INT, Column))

edited Dec 8, 2024 at 14:42

answered Dec 8, 2024 at 14:40

Hari

11 bronze badge

1 Comment

Edward Dec 13, 2024 at 22:22

this looks like it will truncate decimal values (e.g. 1.2 would become 1.0)

Collectives™ on Stack Overflow

Converting varchar values to decimal while handling NULLs and EMPTY strings as 0

4 Answers 4

2 Comments

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related