CODEWITHSUNDEEP
sqlsql-server
codeulike
codeulike

Reputation: 23064

T-SQL - Why does REPLACE not work properly within Case?

See this SQL Fiddle: http://sqlfiddle.com/#!3/068e9/3/0
(MS Sql Server 2008)

Setup of schema is:

create table test_replace ( something varchar(50));

insert into test_replace(something) values('072 782 5343');
insert into test_replace(something) values('071 647 2342');
insert into test_replace(something) values('[email protected]');

Query is:

select case when CHARINDEX('@', something,0) > -1
then REPLACE(something,'@','@testtesttest')
else REPLACE(something,' ','') end as test1,
REPLACE(something,' ','') as test2
from test_replace

The second REPLACE in the CASE statement does not work (compare output of test1 to test2 when applied to the phone numbers). Why is that?

Upvotes: 0

Views: 562

Answers (2)

ASh
ASh

Reputation: 35720

CHARINDEX('@', '072 782 5343',0) not -1 but 0

so change -1 to 0 in condition

select case when CHARINDEX('@', something,0) > 0
then REPLACE(something,'@','@testtesttest')
else REPLACE(something,' ','') end as test1,
REPLACE(something,' ','') as test2
from test_replace

Upvotes: 2

Ross McNab
Ross McNab

Reputation: 11577

CHARINDEX returns 0 when the string is not found (not -1 as you have)

Your query should be:

select case when CHARINDEX('@', something,0) > 0
then REPLACE(something,'@','@testtesttest')
else REPLACE(something,' ','') end as test1,
REPLACE(something,' ','') as test2
from test_replace

Upvotes: 4

Related Questions