CODEWITHSUNDEEP
sqlsql-server-2008
SeanFlynn
SeanFlynn

Reputation: 453

SQL Replace Using Join with Like

I am using SQL 2008.

tblComments is a data table that stores user comments. tblErrorNumbers is a lookup table that has a finite list of errors and their associated description. Couple examples:

Error Number | Error Description
100          | Unreadable  
200          | Printer Error

I have been asked to find all references to error numbers in the comments field and replace them with the error description. So, for example, replace 

Blah 100 Blah

with 

Blah Unreadable Blah

What I have below works only as long as there is only one error number match in the table. But... if I have this: 

Blah 100 Blah 200 Blah

The result set is: 

Blah 100 Blah Printer Error Blah

Why does this happen?? What can I do to replace ALL references of the number with the description? Any help appreciated. Thanks!

SQL:

select 

a.comments as originalString,
REPLACE (a.comments, b.errorNumber, b.errorDescription) As newString


from tblComments a

Left Join tblErrorNumbers b ON a.comments LIKE '%' + b.errorNumber + '%'

Upvotes: 1

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Alas, the replace() only works on one comment at a time. And, they are not nested. Each is replaced independently.

One solution is to use recursive CTEs for this. I think the following should give an example of how to do this:

with ErrorNumbers as (
      select row_number() over (order by ErrorNumber) as seqnum, en.*
      from tblErrorNumbers en
     ),
     cte as (
      select replace(c.comments, en.errorNumber, en.errorDescription) as comments,
             1 as seqnum,
             (select max(seqnum) from ErrorNumbers) as maxseqnum
      from tblComments c left outer join
           ErrorNumbers en
           on en.seqnum = 1
      union all
      select replace(cte.comments, en.errorNumber, en.errorDescription) as comments, 
             cte.seqnum + 1, cte.maxseqnum
      from cte join
           ErrorNumbers en
           on en.seqnum = cte.seqnum + 1
      where cte.seqnum <= cte.maxseqnum
     )
select distinct comments
from cte;

Upvotes: 3

Related Questions