REPLACE empty string

Question

I discover some behavior I didn't know before. Why this line of code does not work?

SELECT REPLACE('','','0') ==> returns ''

I can't even have '' in where condition. It just doesn't work. I have this from imported Excel where in some cells are no values but I'm not able to remove them unless I used LEN('') = 0 function.

I tried to set in where condition an empty string and in my case it works. — Galma88
– Galma88, Commented Mar 12, 2015 at 13:34
@Galma88: it does not work as expected (from OP). It does not replace the empty string with 0. — Tim Schmelter
– Tim Schmelter, Commented Mar 12, 2015 at 13:41

Tim Schmelter · Accepted Answer · 2015-03-12 13:36:00Z

22

There is nothing to replace in an empty string. REPLACE replaces a sequence of characters in a string with another set of characters.

You could use NULLIF to treat it as NULL + COALESCE (or ISNULL):

declare @value varchar(10);
set @value = '';
SELECT COALESCE(NULLIF(@value,''), '0')

This returns '0'.

answered Mar 12, 2015 at 13:36

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

Comments

Julian · Accepted Answer · 2019-10-17 19:49:05Z

4

You can use CASE for this.

(CASE WHEN *YOURTHING* = '' THEN '0' ELSE *YOURTHING* END) 
AS *YOURTHING*

edited Oct 17, 2019 at 19:49

Julian

37.3k24 gold badges138 silver badges196 bronze badges

answered Oct 17, 2019 at 19:42

zookymonsta

411 bronze badge

Comments

iced · Accepted Answer · 2015-03-12 13:35:12Z

0

It does work. There are two proper behaviors here - first one is to return back empty string (what it does already), second one is to return infinite string full of zeroes.

answered Mar 12, 2015 at 13:35

iced

1,5828 silver badges10 bronze badges

1 Comment

Tim Schmelter Over a year ago

He doesn't want to return an empty string from an already empty string. He wants to replace it with 0.

Abdul Khaliq · Accepted Answer · 2020-09-21 07:54:30Z

0

Solved! > Check multiple scenarios like '', remove spaces, Null, string/numeric result

 SELECT CASE WHEN LTRIM(RTRIM(ISNULL(mob, 0))) = '' THEN '0' ELSE LTRIM(RTRIM(ISNULL(mob, 0)))  END  MobileNo 
FROM table1 WHERE emp_no = '01111'

answered Sep 21, 2020 at 7:54

Abdul Khaliq

2,2894 gold badges29 silver badges32 bronze badges

Collectives™ on Stack Overflow

REPLACE empty string

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related