SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


replace single quotes in select statement


replace single quotes in select statement

Author
Message
Marcus Farrugia
Marcus Farrugia
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 505
Hi, I am building a table with a column that stores query's from my reporting service deck. However; I am running into a problem with single quotes in my query's. I was trying to use the replace function to to space out the quotes or to format them for insertion into my table something like this:

insert into table1 (query)
values('select replace('select * from table 2 where something in ('a', 'b', 'c')', ''', ''))

I am getting an 'Incorrect Syntax error near 'a'' on line 3. Does someone know of a way I can get around this in order to insert my query's into my table?
Thank you.
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3595 Visits: 3889
Hi Marcus,

your query looks a little messed up.
Try to rewrite it from scratch.

To replace singlequotes, use this format: REPLACE('stringxy' ,'''','')

Best Regards,

Chris Büttner
James Raddock
James Raddock
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 102
I think what you're trying to do is insert a SQL string into a varchar text field.

You have to replace all the single quotes with '' to escape them [that's 2 single quotes]

Like so:

INSERT INTO
FOOD(Query)
VALUES
('Wheatie''s')
James Raddock
James Raddock
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 102
Also, I know that Wheaties has no apostrophe....
steveb.
steveb.
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4424 Visits: 7195
The best way to do this would be to use variables in your stored proc
instead of


insert into table1 (query)
values('select replace('select * from table 2 where something in ('a', 'b', 'c')', ''', ''))



try putting your values into variables

declare @ValueA varchar(20)
declare @ValueB varchar(20)
declare @ValueC varchar(20)


Insert into Table2
values (@ValueA, @ValueB, @ValueC)




use the variables as input parameters for your stored procedure and this should solve your problem
John Rowan
John Rowan
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6110 Visits: 4551
Where are you reading the query in from?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7791 Visits: 9971
Here is one way:

-- Setup the variable for testing
Declare @value varchar(1000);
Set @value = 'select * from table 2 where something in (''a'', ''b'', ''c'')';

Select @value; -- this will look like select * from table 2 where something in ('a', 'b', 'c')
Select quotename(@value, ''''); -- [i]'select * from table 2 where something in (''a'', ''b'', ''c'')

Jeff

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Marcus Farrugia
Marcus Farrugia
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 505
Hello all and thanks for responding, I wasn't able to get this to work for me. We are building a table that contains all our query's on our reporting services deck. An example of a query would be:

SELECT [Key Measure], Indicator, AVG(Target) AS Target, AVG(Results) AS Results, Period,
CASE WHEN Period = 'JAN' THEN 01 WHEN Period = 'FEB' THEN 02 WHEN Period = 'MAR' THEN 03 WHEN Period = 'APR' THEN 04 WHEN Period = 'MAY'
THEN 05 WHEN Period = 'JUN' THEN 06 WHEN Period = 'JUL' THEN 07 WHEN Period = 'AUG' THEN 08 WHEN Period = 'SEP' THEN 09 WHEN Period =
'OCT' THEN 10 WHEN Period = 'NOV' THEN 11 WHEN Period = 'DEC' THEN 12 WHEN Period = 'YTD' THEN 13 END AS [Monthly Order]
FROM vu_All_Data_DashboardSummary_08_WithTargets
WHERE (NPA IN ('Ontario', 'Ontario Aliant', 'Ontario Bell'))
GROUP BY [Key Measure], Indicator, Period


what I would like to do is write an insert statement that I can copy the query in as a varchar data type into the value of the query column. The problem I was running into was the single quotes. We have a manual work around. Just trying to figure out a way to do it through scripting, but haven't been able to. Thanks for all responses.
James Raddock
James Raddock
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 102
Just do a search and replace for each single quote (') and make it 2 single quotes (''). 2 single quotes escapes one
Anam Verma
Anam Verma
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 1318
James is right. If your query is not in any kind of variable, then only way out is to copy it in any text editor and replace 1 single quote (') with 2 single quotes ('').
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search