SQL Replace function using CharIndex Error

Question

select REPLACE(comments,substring(comments, 
charindex('statement',comments), LEN(comments)),'') 
from Customer 
where charindex('statement',comments) <>0

I want to replace only "statement" from column comments using Replace function but it replaces records to ' ' which doesn't have "Statement" string in it.

James · Accepted Answer · 2016-11-11 04:20:33Z

3

Pls try like this, this will only replace the string statement from the comments :

declare @Customer table(comments nvarchar(max))
insert into @Customer values('this is a statement'), ('statement is this'), ('this')


select case when charindex('statement',comments) <> 0 then REPLACE(comments,substring(comments, charindex('statement',comments), LEN('statement')),'')
else comments end from @Customer 

select * from @Customer

answered Nov 11, 2016 at 4:20

James

7276 silver badges10 bronze badges

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

Comments

Rahul · Accepted Answer · 2016-11-11 04:11:30Z

1

You should probably change that to use > greater than comparison

where charindex('statement',comments) > 0

(OR) Use like operator

where comments like '%statement%'

answered Nov 11, 2016 at 4:11

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

2 Comments

BI_Reporting Over a year ago

Yes I tried both of them but it still updates records to ' ' which do not contain string 'Statement' in it

Rahul Over a year ago

@DipenShah, post sample data where statement not present and it does the update still.

Collectives™ on Stack Overflow

SQL Replace function using CharIndex Error

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