I'm storing some queries in a table column so I can execute them later passing some parameters.
But it has been really annoying to format the query into an Update sentence, because of the special characters.
For Example:
SELECT * FROM MOUNTAINS WHERE MON_NAME='PALMA' AND MON_DESC LIKE '%TRANVULCANIA%'
Then I need the string just for the udpate query:
UPDATE QUERIES
SET QUE_SEL='SELECT * FROM MOUNTAINS WHERE MON_NAME='''+'PALMA'+''' AND MON_DESC LIKE '''+'%TRANVULCANIA%'+''' '
WHERE QUE_ID=1
as you can see the first ' must be replaced for '''+' but the next door ' must be replaced by '+'''
This is the query I'm working on:
DECLARE @QUERY VARCHAR(MAX)
SELECT @QUERY='SELECT * FROM QUERIES WHERE QUE_NOMBRE='''+'PRUEBA 1'+''' '
SELECT
t.r.value('.', 'varchar(255)') AS token
, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id
FROM (
SELECT myxml = CAST('<t>' + REPLACE(@QUERY, '''', '</t><t>''</t><t>') + '</t>' AS XML)
) p
CROSS APPLY myxml.nodes('/t') t(r)
this is the result:
token id
-------------------------------------------------- --------------------
SELECT * FROM QUERIES WHERE QUE_NOMBRE= 1
' 2
PRUEBA 1 3
' 4
5
Now I want a column that tell me when to open and when to close and then I can set the final replace.
Adapting the solution given by @rivarolle
DECLARE @QUERY VARCHAR(MAX)
DECLARE @FORMATTED varchar(max)
SELECT @QUERY='SELECT * FROM QUERIES WHERE QUE_NOMBRE='''+'PRUEBA 1'+''''
;WITH TOKENS AS(
SELECT
t.r.value('.', 'varchar(MAX)') AS token
, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Id
FROM (
SELECT myxml = CAST('<t>' + REPLACE(@QUERY, '''', '</t><t>''</t><t>') + '</t>' AS XML)
) p
CROSS APPLY myxml.nodes('/t') t(r)
)
,
Tokens2 as (
SELECT
TOKENS.token as token
,quotes.row%2 as tipoapostrofe
from Tokens
left join (select row_number() over( order by Id asc) as row, a.* FROM (SELECT * from Tokens) a where Token = '''') quotes
on quotes.Id = Tokens.Id
)
SELECT @FORMATTED = STUFF((
SELECT ' ' + REPLACE(token,'''',CASE tipoapostrofe WHEN 1 THEN '''''''+''' WHEN 0 THEN '''+''''''' ELSE '' END) AS [text()]
FROM Tokens2
FOR XML PATH('')
), 1, 1, '')
print @FORMATTED
This Works, just need a function for cleaning XML special characters and another for putting back, and the Dynamic queries are printed ready for an update.
I think its not necessary to replace an apostrophe with '''+' to open and '+''' to close, I made some probes and you can exec a query that you replace opening and closing apostrophes with the same.. for example '''+' for open and '''+' for close.
So the query would be:
DECLARE @QUERY VARCHAR(MAX)
DECLARE @FORMATTED varchar(max)
SELECT @QUERY='SELECT * FROM QUERIES WHERE QUE_NOMBRE='''+'PRUEBA 1'+''''
SELECT @FORMATTED= STUFF((
SELECT ' ' +
(SELECT
CASE
WHEN t.r.value('.', 'varchar(250)')='''' THEN REPLACE(t.r.value('.', 'varchar(250)'), '''','''''''+''')
ELSE t.r.value('.', 'varchar(250)')
END
) AS [text()]
-- , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id
FROM (
SELECT myxml = CAST('<t>' + REPLACE(@QUERY, '''', '</t><t>''</t><t>') + '</t>' AS XML)
) p
CROSS APPLY myxml.nodes('/t') t(r)
FOR XML PATH('')
), 1, 1, '')
SET @FORMATTED=REPLACE(@FORMATTED,' ','')
PRINT @FORMATTED
then I get:
SELECT * FROM QUERIES WHERE QUE_NOMBRE= '''+' PRUEBA 1 '''+'
then I copy into a variable and execute
DECLARE @VAR VARCHAR(500)
SET @VAR='SELECT * FROM QUERIES WHERE QUE_NOMBRE='''+'PRUEBA 1'''+' '
EXEC(@VAR)
It Works for very simple queries, but with longer and complicated queries it doesn't works..
Assuming your token table is Tokens(Token, Id, Position):
update Tokens
set position = quotes.row%2
from Tokens
left join (select row_number() over( order by Id asc) as row, a.* FROM (SELECT * from Tokens) a where Token = '''') quotes
on quotes.Id = Tokens.Id
The position column will have a value of 1 for starting quote and 0 for closing quote. NULL for the rest.
