CODEWITHSUNDEEP
sqlsql-server
user1331734
user1331734

Reputation: 87

SQL Server Search for multiple instances of same text in column

I have a SQL Server table that contains an nvarchar(max) column (MyText) containing sentences. I need to identify all instances of a particular phrase in all rows of the (MyText) column. Once identified I want to replace all instances with different text.

Thanks, Brad

select cust_div, cust_seral 
from [dbo].[lveIntake_closing_scripts] 
where close_script like '%LMLSUnit%LMLSUnit.com%'

Upvotes: 0

Views: 496

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

To count how many instances of the source string is contained within each row, you need to replace each instance with a string that is one character shorter, then subtract that length of the resultant string from the length of the original string. Like this:

select
     cust_div
   , cust_seral 
   , len(close_script) - len(replace(close_script, 'LMLSUnit.com','LMLSUnit.co'))
from [dbo].[lveIntake_closing_scripts] 
where close_script like '%LMLSUnit%LMLSUnit.com%'

Upvotes: 2

Related Questions