October 28, 2013 at 6:16 am
Hi all
How can I escape the apostrophes when I am using the variables inside a .net script?
See below:
I have a table which contains a column (VARCHAR) [FileSystemPath], amongst others, the FileSystemPath column has data stored which includes apostrophes in the text eg:
Alistair_McGowan's Big Impression_S4_Captions_Jan_2013_550585858.doc
Now when I query this table the results are returned just fine.
SELECT FileSystemPath from TABLE
\\wwfis1012a\DocumentStore\Sku\ABAYY122L\Alistair_McGowan's Big Impression_S4_Captions_Jan_2013_550585858.doc
Which is fine Smile
However I need to get another value [OriginalName] based on the filesystempath
SELECT [OriginalName] from Table
Where FileSystemPath = 'Alistair_McGowan's Big Impression_S4_Captions_Jan_2013_550585858.doc'
Which gives me the expected error: Incorrect syntaxt near 's Big_' Unclosed quotation mark after the character string '
Now, of course, normally if I was running this query I would simply escape the ' in the McGowan's and everything would be fine...
However I am doing all of this query inside a .net script within SSIS, I have around 250,000 records to go through.
Here is my script below the important thing here is the variable User::SingleFileName is the aforementioned Alaistair McGowan etc.,
Dim fileSystemPath As String
fileSystemPath = Dts.Variables("User::SingleFileName").Value
Dts.Variables("User::destNameSQLQuery").Value = "" & _
"select [OriginalName] from [Bbc.Ww.Wam.Migration2012.Database].dbo.[PreSalesSKU_ForRename] " & _
"where FileSystempath = '" & fileSystemPath & "'"
Which equates to
select [OriginalName] from [Bbc.Ww.Wam.Migration2012.Database].dbo.[PreSalesSKU_ForRename]
where FileSystempath =
'wwlis1021a\\DocumentStore\\Sku\\ABAY122L\\Alistair_McGowan's_Big_ Impression_S4_Captions_Jan_2013_634940195179370992.doc'
which of course fails. 🙁
So my question is: How can I escape the apostrophes when I am using the variables inside a .net script?
Any help greatly greatly received.
Paul
October 28, 2013 at 6:31 am
Paul, surely you just use the REPLACE function (or its .Net equivalent) on FileSystempath to replace each single quote with two single quotes?
John
October 28, 2013 at 8:23 am
Yes I did 🙂 I can't believe how much time I spent trying to find something 'clever'! What a plumb!
Thanks for your suggestion
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply