T-SQL - Why REPLACE Does Not Work Correctly in Case?

advertisements

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?


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