Unable to replace an empty string in SQL Server 2017

Question

I have a table with a varchar(100) column that has some empty values. When I try to replace the empty strings with a value, nothing happens. I cannot figure out why. I am sure the value in the cells is not null. I can reproduce with a temporary table and some sample data.

The below SQL should return a result set in which the [ReplacedTextCol] always has a value, but even when using replace, I cannot replace the empty string:

create table #tempt
(
    col1 varchar(100)
)

insert into #tempt(col1) 
values('')
,('')
,('some value')

select replace(col1, '','replaced') as ReplacedTextCol,* 
from #tempt

The result of the above query is shown below:

Table, DB & server uses the collation SQL_Latin1_General_CP1_CI_AS

Appreciate any pointer as to where I'm going wrong.

If you think about it, it's quite logical. An empty string has no characters. How many 0-length substrings are in abcd? What you ask should either match all characters as if it were a wildcard, or none — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 23, 2020 at 16:41
Use case when or IIF() instead of REPLACE. You're looking for an exact match after all, not find an empty string inside another string. Eg use IIF(col1='','replaced',col1) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 23, 2020 at 16:42

gofr1 · Accepted Answer · 2020-09-23 17:24:08Z

3

REPLACE doesn't work with empty strings:

string_pattern

Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern cannot be an empty string (''), and must not exceed the maximum number of bytes that fits on a page.

Use simple UPDATE:

UPDATE YourTable
SET SomeColumn = 'replaced'
WHERE SomeColumn  = '';

Or SELECT:

SELECT CASE WHEN SomeColumn = '' THEN 'replaced' ELSE SomeColumn END
FROM YourTable
WHERE SomeColumn  = ''

answered Sep 23, 2020 at 17:24

gofr1

16k11 gold badges47 silver badges56 bronze badges

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

Comments

Robert Sheahan · Accepted Answer · 2020-09-23 16:34:12Z

0

REPLACE is used to replace one substring with another, you want COALESCE or a SELECT CASE construct to replace empty values, try

select CASE WHEN COALESCE(col1,'') = '' THEN 'replaced' ELSE col1 END as ReplacedTextCol,* 
from #tempt

answered Sep 23, 2020 at 16:34

Robert Sheahan

2,0851 gold badge11 silver badges13 bronze badges

2 Comments

Panagiotis Kanavos Over a year ago

That's not what COALESCE is for, or what your own answer uses. This answer uses CASE WHEN to return a different value if a field contains an empty string. COALESCE replaces a null value with a specific value, which isn't needed here

Robert Sheahan Over a year ago

COALESCE replaces a value when empty is represented as NULL, if you might have some NULL and some as an empty string (which COALESCE won't touch) you need both COALESCE and CASE, the COALESCE converts NULL to EMPTY, and the CASE checks for EMPTY and converts to the final value. Depending on the source of data, mixed NULL and EMPTY might be a big problem and this works for all.

Collectives™ on Stack Overflow

Unable to replace an empty string in SQL Server 2017

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related