question

JamesB avatar image
JamesB asked

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-sqltsqlreplace
capture.jpg (134.1 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
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 characters needed characters left characters exceeded

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

erlokeshsharma08 avatar image
erlokeshsharma08 answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
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 Likes 0 ·
after end keyword you should give the name to the column as end as 'columnname'. Hope it helps, Thanks
0 Likes 0 ·
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 Likes 0 ·
How many columns you want in your output along with names please?
0 Likes 0 ·
Show more comments
erlokeshsharma08 avatar image
erlokeshsharma08 answered
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 characters needed characters left characters exceeded

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

JamesB avatar image
JamesB answered
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 characters needed characters left characters exceeded

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

JamesB avatar image
JamesB answered
This works well in prod for some time already. Thanks for the effort, and sorry for only answering now.
10 |1200 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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