replace single quotes in select statement

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » replace single quotes in select statement

17 posts, Page 1 of 2

1 2 »»

replace single quotes in select statement

Rate Topic

Display Mode

Topic Options

Author

Message

Marcus Farrugia

Posted Monday, June 02, 2008 1:44 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 02, 2013 6:08 AM
Points: 139, Visits: 276

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

Christian Buettner-167247

Posted Monday, June 02, 2008 1:58 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 2,526, Visits: 3,620

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

James Raddock

Posted Tuesday, June 03, 2008 4:57 AM

SSC 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

James Raddock

Posted Tuesday, June 03, 2008 4:58 AM

SSC 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

steveb.

Posted Tuesday, June 03, 2008 5:20 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

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

John Rowan

Posted Tuesday, June 03, 2008 4:17 PM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821

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

Jeffrey Williams 3188

Posted Tuesday, June 03, 2008 4:53 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216

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

Marcus Farrugia

Posted Wednesday, June 04, 2008 7:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 02, 2013 6:08 AM
Points: 139, Visits: 276

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

James Raddock

Posted Wednesday, June 04, 2008 8:15 AM

SSC 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

Anam Verma

Posted Wednesday, June 04, 2008 11:53 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:53 PM
Points: 524, Visits: 1,181

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 »

17 posts, Page 1 of 2

1 2 »»

Permissions