Using Replace to add a single quote SQL

[Chuck712]

I have a field in a survey results table that was populated with jscript code for the recommended action based on a set of answers. The field name is g.Risk_Validation. Data inside looks like this :

{n1} == 7C2A4E1F-D2AB-4263-A1D5-78B93C4C0E90 || {n1} == B5F6DDC7-FCF2-4209-8F1C-D974B2158AAE || {n1} == 58323A4B-28E9-4FC7-9BB5-E59AAA34617D.

I am trying to use a replace statement to extract the un-needed characters and put single quotes around the key inside. 7C2A4E1F-D2AB-4263-A1D5-78B93C4C0E90 would be the first key for example.

Here is the replace statement that I have but I am stuck on surrounding the key with single quotes.

replace(replace(replace(g.Risk_Validation, '||', 'or'), '==', '='), '{n1}', '''' + CONVERT(NVARCHAR(36),u13_u05_key) + '''') end as RiskVal_Replace

The u13_u05_key will map to a text field that will show me the answers. Any help would be appreciated.

 

[gmmastros]

Maybe I don't understand what you are trying to accomplish because when I hard code some values and use your replace code, everything appears to work ok.

Declare @Data VarChar(1000)
Declare @u13_u05_key varchar(100)

Set @Data = '{n1} == 7C2A4E1F-D2AB-4263-A1D5-78B93C4C0E90 || {n1} == B5F6DDC7-FCF2-4209-8F1C-D974B2158AAE || {n1} == 58323A4B-28E9-4FC7-9BB5-E59AAA34617D.'
Set @u13_u05_key = '7C2A4E1F-D2AB-4263-A1D5-78B93C4C0E90'

Select replace(replace(replace(@Data, '||', 'or'), '==', '='), '{n1}', '''' + CONVERT(NVARCHAR(36),@u13_u05_key ) + '''')

Result is:

[tt]'7C2A4E1F-D2AB-4263-A1D5-78B93C4C0E90' = 7C2A4E1F-D2AB-4263-A1D5-78B93C4C0E90 or '7C2A4E1F-D2AB-4263-A1D5-78B93C4C0E90' = B5F6DDC7-FCF2-4209-8F1C-D974B2158AAE or '7C2A4E1F-D2AB-4263-A1D5-78B93C4C0E90' = 58323A4B-28E9-4FC7-9BB5-E59AAA34617D.[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chuck712]

Thanks. I think I found the solution. I was trying not to send a huge statment that I would have to de identify. But I got the string to work. The field we were evaluating needed the quotes. I appreciate the help.