CODEWITHSUNDEEP
sql-serverstored-procedures
Bill
Bill

Reputation: 1477

Format a portion of a string in a stored procedure

I have a string that is produced in a SQL Server function and it is returned like this:

          Ouseph MM, Li J, Chen HZ, Pécot T, Wenzel P, Thompson JC, Comstock 
          G, Chokshi V, Byrne M, Forde B, Chong JL, Huang K, Machiraju R, de 
          Bruin A, Leone G (2012) Atypical E2F repressors and activators 
          coordinate placental development. <i>Dev Cell</i>. 22(4):849-62 
          PMCID:PMC3483796

What I need to do it to bold with HTML tags a couple of the names that are stored in another table. The names could be:

        Leone GW
        Leone G
        Thompson J
        Thompson JC

This is the different ways that the names could exist. Do I need to create a cursor and loop? Then I also have the problem of names with one initial or both. In this example I need to replace Leone G with <b>Leone G</b> and Thompson JC with <b>Thompson JC</b>

Any help or suggestions are appreciated

Upvotes: 1

Views: 569

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82020

Here is one quick approach... essentially a tokenized substitution.

Note: You would have to migrate this into a UDF if you wanted to apply this technique to an entire table.

EDIT:

You may notice the CROSS JOIN in the subquery. Here you would have to add any expected trailing delimiter

Example

Declare @YourTable Table ([SomeCol] varchar(50))
Insert Into @YourTable Values 
 ('Leone GW')
,('Leone G')
,('Thompson J')
,('Thompson JC')


Declare @S varchar(max) = 'Ouseph MM, Li J, Chen HZ, Pécot T, Wenzel P, Thompson JC, Comstock G, Chokshi V, Byrne M, Forde B, Chong JL, Huang K, Machiraju R, de Bruin A, Leone G (2012) Atypical E2F repressors and activators coordinate placental development. <i>Dev Cell</i>. 22(4):849-62 PMCID:PMC3483796'

Select @S=Replace(@S,MapFrom,MapTo)
 From  (
        Select MapFrom = SomeCol+suff
              ,MapTo   = '<b>'+SomeCol+'</b>'+suff
         From  @YourTable B1
         Cross Join (values (' ')
                           ,(',')
                           ,('.')
                    ) B2 (suff)
       ) A

Select @S

Returns

Ouseph MM, Li J, Chen HZ, Pécot T, Wenzel P, Thompson JC Comstock G, Chokshi V, Byrne M, Forde B, Chong JL, Huang K, Machiraju R, de Bruin A, Leone G(2012) Atypical E2F repressors and activators coordinate placental development. Dev Cell. 22(4):849-62 PMCID:PMC3483796

Upvotes: 4

Related Questions