SQL bypass REPLACE by CASE statement

1.8k views Asked by At

I have data where some records contain blank rows (=no space, no NULL). E.g.:

LOCALE
en-es
en-uk

uk-uk

When I want to select that blank row, it's easy with:

SELECT LOCALE
FROM ABC
WHERE LOCALE = ''

But when I try to replace it as follows, it does not work, the result is still blank row:

SELECT REPLACE(LOCALE,'','WHY') AS 'LOCALE'
FROM ABC
WHERE LOCALE = ''

But if I bypass this with CASE statement, it works:

SELECT CASE
       WHEN LOCALE LIKE '' THEN 'WHY'
       ELSE LOCALE
       END AS 'LOCALE'
FROM ABC
WHERE LOCALE = ''

What is the problem here? Why does REPLACE function not work?

4

There are 4 answers

4
ThePravinDeshmukh On BEST ANSWER

It is obvious that '' means blank (Not null) and it exists between two consecutive letters also. (As it is blank)

So Sql can not go on replacing that blank in between every letter with the string you want. That's why you can not replace ''

Check this query

SELECT ISNULL(NULLIF(LOCALE, ''),'WHY') AS [LOCALE]
FROM ABC
WHERE LOCALE = ''
0
Mukesh Kalgude On

Your replace function not replace blank space. Try to this

SELECT REPLACE(isnull(ITEM,''),' ','WHY') AS 'LOCALE'
FROM Table1
WHERE ITEM = ''
0
Arunprasanth K V On

This is only an alternative method try the below method

select COALESCE(NULLIF('',''), 'WHY' AS 'LOCALE'
FROM ABC
WHERE LOCALE = ''
0
Stanislovas Kalašnikovas On

I suggest for you to use NULLIF to convert empty string to NULL, after It to use COALESCE (ANSI standard) to replace NULL to any value you want, in following:

SELECT COALESCE(NULLIF(LOCALE, ''),'WHY') AS 'LOCALE' 
FROM ABC 
WHERE LOCALE = ''