CODEWITHSUNDEEP
sqlsql-serversql-server-2008
BI_Reporting
BI_Reporting

Reputation: 65

SQL Replace function using CharIndex Error

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.

Upvotes: 2

Views: 1330

Answers (2)

James
James

Reputation: 729

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

Upvotes: 3

Rahul
Rahul

Reputation: 77926

You should probably change that to use > greater than comparison 

where charindex('statement',comments) > 0

(OR) Use like operator

where comments like '%statement%'

Upvotes: 1

Related Questions