question

JamesB asked May 26, '15

Multiple in string replacement

[link text][1]**Background:** As per the table below (See data and/or screen capture and/or data download), in which 3 examples are shown, I need to replace multiple values in a string with values from another column - replace as part a query for reporting/display purposes etc and not updating the DB data. The first block of data (11 rows) has the contents of my table in the DB. Note the RecordId field to differentiate between the 3 'data sets'. The last 3 rows show the result I am looking for. **The Question:** Remembering the RecordID field value as the differentiator for every 'data set': I need to change the %1 in the 'MsgText' column with the value in 'InsStrValue' column where 'InsStrIndex' = 0 I need to change the %2 in the 'MsgText' column with the value in 'InsStrValue' column where 'InsStrIndex' = 1 I need to change the %3 in the 'MsgText' column with the value in 'InsStrValue' column where 'InsStrIndex' = 2 I need to change the %4 in the 'MsgText' column with the value in 'InsStrValue' column where 'InsStrIndex' = 3 I need to change the %5 in the 'MsgText' column with the value in 'InsStrValue' column where 'InsStrIndex' = 4 ....etc. The number of string variables vary between 'record sets' to be changed can be as few as 1 in certain cases and as much as 12 in others. Note that the 'InsStrIndex' is one less that the '%x' string value that needs to be replaced. I am too new to T-SQL to master this find-and-repalce, and any guidance and assistance would be highly appreciated. *** *** Data in CSV format attached in *.zip *** *** Screen capture of data table below data. **The Data**

MsgText

RecordID

InsStrIndex

InsStrValue

User "%1" created class security rights for user "%3" on object "%2".Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12

117877975

0

MyDomain\user022c

User "%1" created class security rights for user "%3" on object "%2".Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12

117877975

1

3

User "%1" created class security rights for user "%3" on object "%2".Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12

117877975

2

MyDomain\user012d

User "%1" created class security rights for user "%3" on object "%2".Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12

117877982

0

MyDomain\user068u

User "%1" created class security rights for user "%3" on object "%2".Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12

117877982

1

10

User "%1" created class security rights for user "%3" on object "%2".Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12

117877982

2

MyDomain\user017x

User "%2" from computer "%3" created a session using application [%4] version [%5].%12

117878195

0

MyDomain\user015t

User "%2" from computer "%3" created a session using application [%4] version [%5].%12

117878195

1

MyDomain\user0157

User "%2" from computer "%3" created a session using application [%4] version [%5].%12

117878195

2

ServerName

User "%2" from computer "%3" created a session using application [%4] version [%5].%12

117878195

3

Siscus

User "%2" from computer "%3" created a session using application [%4] version [%5].%12

117878195

4

4.0.6000.0

User "MyDomain\user022c" created class security rights for user "MyDomain\user012d" on object "3".Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.

117877975

User "MyDomain\user068u" created class security rights for user "MyDomain\user017x" on object "10".Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.

117877982

User "MyDomain\user015t" from computer "ServerName" created a session using application [Siscus] version [4.0.6000.0].

117878195

![alt text][2] [1]: /storage/temp/2456-instrvalue-example.zip [2]: /storage/temp/2455-capture.jpg

t-sql tsql replace

capture.jpg (134.1 KiB)

instrvalue-example.zip (564 B)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab ♦ commented · Jun 25, 2015 at 07:09 PM

Thanks for the challenge. It took a little while to come up with my answer, but it was fun. I knew a pivot would work; it just took time to write it all out and test it.

0 ·

Tom Staab answered Jun 25, '15

This took a little while, but having your sample data made it relatively easy to test. Please try the following code. Note: I changed your search strings to always be 2 digit numbers (i.e. %01, %02, etc.) to avoid confusion between %1 and %10 through %12. I hope this works well for you. IF OBJECT_ID('tempdb..#Messages') is not null DROP TABLE #Messages; CREATE TABLE #Messages ( MsgText varchar(500) , RecordID int , InsStrIndex int , InsStrValue varchar(100) ); INSERT #Messages (MsgText, RecordID, InsStrIndex, InsStrValue) VALUES ('User ''%01'' created class security rights for user ''%03'' on object ''%02''.Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12', 117877975, 0, 'MyDomain\user022c') , ('User ''%01'' created class security rights for user ''%03'' on object ''%02''.Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12', 117877975, 1, '3') , ('User ''%01'' created class security rights for user ''%03'' on object ''%02''.Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12', 117877975, 2, 'MyDomain\user012d') , ('User ''%01'' created class security rights for user ''%03'' on object ''%02''.Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12', 117877982, 0, 'MyDomain\user068u') , ('User ''%01'' created class security rights for user ''%03'' on object ''%02''.Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12', 117877982, 1, '10') , ('User ''%01'' created class security rights for user ''%03'' on object ''%02''.Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.%12', 117877982, 2, 'MyDomain\user017x') , ('User ''%02'' from computer ''%03'' created a session using application [%04] version [%05].%12', 117878195, 0, 'MyDomain\user015t') , ('User ''%02'' from computer ''%03'' created a session using application [%04] version [%05].%12', 117878195, 1, 'MyDomain\user0157') , ('User ''%02'' from computer ''%03'' created a session using application [%04] version [%05].%12', 117878195, 2, 'ServerName') , ('User ''%02'' from computer ''%03'' created a session using application [%04] version [%05].%12', 117878195, 3, 'Siscus') , ('User ''%02'' from computer ''%03'' created a session using application [%04] version [%05].%12', 117878195, 4, '4.0.6000.0') , (null, null, null, null) , (null, null, null, null) , ('User ''MyDomain\user022c'' created class security rights for user ''MyDomain\user012d'' on object ''3''.Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.', 117877975, null, null) , ('User ''MyDomain\user068u'' created class security rights for user ''MyDomain\user017x'' on object ''10''.Object types: 1=Grouping, 2=InfoSet, 3=WorkOrder, 4=StatusMessages, 6=Place, 7=Query, 8=Report, 9=Measure, 10=Item.', 117877982, null, null) , ('User ''MyDomain\user015t'' from computer ''ServerName'' created a session using application [Siscus] version [4.0.6000.0].', 117878195, null, null) ; WITH Pivoted AS ( SELECT p.RecordID, p.MsgText , [00] = ISNULL(p.[00], '') , [01] = ISNULL(p.[01], '') , [02] = ISNULL(p.[02], '') , [03] = ISNULL(p.[03], '') , [04] = ISNULL(p.[04], '') , [05] = ISNULL(p.[05], '') , [06] = ISNULL(p.[06], '') , [07] = ISNULL(p.[07], '') , [08] = ISNULL(p.[08], '') , [09] = ISNULL(p.[09], '') , [10] = ISNULL(p.[10], '') , [11] = ISNULL(p.[11], '') , [12] = ISNULL(p.[12], '') FROM (SELECT RecordID, MsgText, InsStrIndex, InsStrValue FROM #Messages) m PIVOT ( MAX(InsStrValue) FOR InsStrIndex IN ([00],[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]) ) p ) SELECT p.RecordID, REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( p.MsgText, '%01', p.[00] ), '%02', p.[01] ), '%03', p.[02] ), '%04', p.[03] ), '%05', p.[04] ), '%06', p.[05] ), '%07', p.[06] ), '%08', p.[07] ), '%09', p.[08] ), '%10', p.[09] ), '%11', p.[10] ), '%12', p.[11] ), '%13', p.[12] ) FROM Pivoted p ;

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erlokeshsharma08 answered May 27, '15

Hi James Please find the sql statement below: update yourtable set MsgText= case when InsStrIndex=0 then replace(MsgText,'%1',InsStrValue) when InsStrIndex=1 then replace(MsgText,'%2',InsStrValue) .....and so on ..... end

8

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JamesB commented · May 27, 2015 at 07:31 AM

I thank you for your reply, I am not however wanting to update the data, I need a query to give the final replaced output for reporting purposes. I will update my question to indicate this more clearly.

0 ·

erlokeshsharma08 commented · May 27, 2015 at 07:46 AM

How about following :- select case when InsStrIndex=0 then replace(MsgText,'%1',InsStrValue) when InsStrIndex=1 then replace(MsgText,'%2',InsStrValue) .....and so on ..... end

0 ·

erlokeshsharma08 commented · May 27, 2015 at 07:48 AM

after end keyword you should give the name to the column as end as 'columnname'. Hope it helps, Thanks

0 ·

JamesB commented · May 27, 2015 at 09:07 AM

This is good to replace one value at a time, but still return a single (replaced) value per line. I am looking to change all the values and then return the full line with all replaced values as per the example; eg RecordID 117877975 returns one line with all the values returned from the original 3 lines; RecordID 117878195 returns one line with all the values returned from the original 5 lines. I do apologize to ask the forum to do the heavy lifting at this point, I am new to TSQL and promise to draw less and contribute more in future as my skills develop

0 ·

erlokeshsharma08 commented · May 27, 2015 at 10:28 AM

How many columns you want in your output along with names please?

0 ·

JamesB commented · May 27, 2015 at 11:09 AM

Two, the MsgText and RecordID please

0 ·

erlokeshsharma08 commented · Jun 05, 2015 at 06:38 AM

Hi James, kind of a tricky one. Will devote some time on it today. Cheers

0 ·

JamesB commented · Jun 24, 2015 at 04:13 PM

any good news perhaps? I am close to begging here :-)

0 ·

erlokeshsharma08 answered Jun 24, '15

Hi mate...really sorry....been busy with work and now on vaccation spending time with family...to be honest I could nt come up with a proper solution, thats why didnt post anything.....will give it a try once more...best of luck with coding bro...

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JamesB answered Aug 10, '15

hi all. thanks for the help. Ma home after an extended trip to almost everywhere. will test in the week and give feedback. I thank you again for your efforts!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JamesB answered Aug 19, '17

This works well in prod for some time already. Thanks for the effort, and sorry for only answering now.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.