Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

replace single quotes in select statement Expand / Collapse
Author
Message
Posted Monday, June 02, 2008 1:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 17, 2014 1:47 PM
Points: 146, Visits: 301
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.
Post #510240
Posted Monday, June 02, 2008 1:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:19 AM
Points: 2,814, Visits: 3,851
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
Post #510258
Posted Tuesday, June 03, 2008 4:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 04, 2010 11:33 AM
Points: 43, 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')
Post #510547
Posted Tuesday, June 03, 2008 4:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 04, 2010 11:33 AM
Points: 43, Visits: 102
Also, I know that Wheaties has no apostrophe....
Post #510549
Posted Tuesday, June 03, 2008 5:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
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
Post #510556
Posted Tuesday, June 03, 2008 4:17 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 12:46 PM
Points: 3,843, Visits: 3,833
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
Post #511017
Posted Tuesday, June 03, 2008 4:53 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 4:08 PM
Points: 4,379, Visits: 9,469
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #511029
Posted Wednesday, June 04, 2008 7:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 17, 2014 1:47 PM
Points: 146, Visits: 301
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.
Post #511379
Posted Wednesday, June 04, 2008 8:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 04, 2010 11:33 AM
Points: 43, Visits: 102
Just do a search and replace for each single quote (') and make it 2 single quotes (''). 2 single quotes escapes one
Post #511411
Posted Wednesday, June 04, 2008 11:53 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:39 AM
Points: 526, Visits: 1,225
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 ('').
Post #511931
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse