SQL bypass REPLACE by CASE statement

Question

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?

Answer 1

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

Answer 2

Your replace function not replace blank space. Try to this

SELECT REPLACE(isnull(ITEM,''),' ','WHY') AS 'LOCALE'
FROM Table1
WHERE ITEM = ''

Answer 3

This is only an alternative method try the below method

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

Answer 4

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