January 28, 2007 at 12:00 pm
Hey everyone,
I have read the forum on single quotes:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=256608
But I cannot get this to work for my requirements.
Crystal Reports is sending across my query and the variable is sends has an apostrophe in it. (I cannot change this)
the sql code is this:
DECLARE @Mth varchar(10)
DECLARE @YR1 varchar(5)
DECLARE @NAME varchar(255)
SET @NAME = '{?Name}'
SET @YR1 ='{?Fiscal_Year}'
SET @Mth ='{?Fiscal_Month}'
The data come across Similar to :
DECLARE @Mth varchar(10)
DECLARE @YR1 varchar(5)
DECLARE @NAME varchar(255)
SET @NAME = 'THIS IS SOMEONE'S EXAMPLE'
SET @YR1 ='2007'
SET @Mth ='December'
I am trying to use the replace function but the apostrophe is really messing me up. I Know I need to get it to a '' to work, but I am stuck!! How can I do this dynamically? I have tried double quotes around it etc. Here is what I am trying:
replace ( 'THIS IS SOMEONE'S EXAMPLE',char(39),char(39)+char(39))
This does not work, put in in Query analyzer and you will see my problem.
Any help or ideas??
-Joe
January 28, 2007 at 12:08 pm
select replace('THIS IS SOMEONE''S EXAMPLE','''','''''') January 28, 2007 at 12:16 pm
That is how to manually do it. The 'THIS IS SOMEONE'S EXAMPLE' comes accross dynamically. I cannot change the single quote to 2 single quotes. This has to be done via SQL. It is coming across from Crystal Reports, so there is no way to use asp, php, javascript etc.
Thanks for the reply tho...
January 28, 2007 at 12:20 pm
I have found a work-around. I can use:
SET QUOTED_IDENTIFIER OFF to force it to work,and the turn the QUOTED_IDENTIFIER back on when the query is complete, but this is NOT my first choice to resolve this issue, due to other queries that may run at the same time.
Anyone???
Thanks!
-Joe
January 28, 2007 at 2:31 pm
CREATE PROC dbo.Blah-blah ..., @NAME varchar(255), ....
AS
...
SET @NAME = replace ( @NAME,char(39),char(39)+char(39))
...
GO
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply