CODEWITHSUNDEEP
sqlsql-server-2008-r2
Muflix
Muflix

Reputation: 6778

REPLACE empty string

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.

Upvotes: 11

Views: 34838

Answers (4)

Abdul Khaliq
Abdul Khaliq

Reputation: 2285

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'

Upvotes: 0

zookymonsta
zookymonsta

Reputation: 41

You can use CASE for this.

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

Upvotes: 4

Tim Schmelter
Tim Schmelter

Reputation: 460058

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'.

Upvotes: 22

iced
iced

Reputation: 1572

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.

Upvotes: 0

Related Questions