Single Quote in SQL Query

[AndrewT]

I am trying to do a SQL query on a csv file that looks like this.

Code: Select all

Select * from %PO_File% where DetailNo = '%PartNo%'

Simple right? The problem is that some of the values for PartNo have a single quote ( ' ) in it that mess up the query. So does anyone have a work around?

I tried to do a StringReplace on the value of PartNo, and then do a REPLACE in my SQL query

Code: Select all

  StringReplace>PartNo,',~,PartNoMod
  Let>SQL=Select * from %PO_File% where REPLACE(DetailNo,'''','~') like '%PartNoMod%'

But I get an error from MS saying "Undefined function 'REPLACE' in expression". I have tried this basic query in a different SQL engine and it works. Is REPLACE not supported when querying a csv file?

Thanks,
Andrew

[JRL]

Never used Replace so I don't know if there is a way to make that work.

Try doing a ReadFile> on the csv file then do a string replace of single quote to tilde. Save the string replaced data to a csv file. Perform your query on the new file. Finally replace tildes with single quotes in the individual query retrieved Partno(s)

Depending on the size of the csv file, possibly won't be as fast as what you're trying to do would be but what you're doing isn't working.

Hope this makes sense.

[JRL]

Set up data to play with so I could test the REPLACE SQL function. Found that I could not make the query fail by placing single quotes in the data. Here is the script I used:

Code: Select all

Let>vFolder=%Temp_Dir%Reports
Let>vFiletoRead=OpenOrders.csv

Let>connstr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%vFolder%;Extended Properties="text;HDR=Yes";
DBConnect>connstr,dbH
Let>qry=Select part_no from %vFiletoRead%
DBQuery>dbH,qry,var,NumOfRecs,NumOfFields
DBClose>dbH

Here is the schema .ini file that I have in the %temp_dir%Reports folder.

Code: Select all

[OpenOrders.csv]
ColNameHeader=True
MaxScanRows=0

I'll continue looking at the REPLACE function to see if I can make it work.

[AndrewT]

I got it!! Turns out alot easier to do.

The problem comes with the "where DetailNo = '%PartNo%' part of the SQL statement. When PartNo has a single quote in it, like "ABCD-5'XL" then the SQL becomes "where DetailNo = 'ABCD-5'XL' " and SQL doesn't like that. But if I replace the single quote with two single quotes, SQL can handle it and it works.

Code: Select all

  GetClipBoard>PartNo
  StringReplace>PartNo,','',PartNoMod
  Let>SQL=Select * from %PO_File% where DetailNo = '%PartNoMod%'

KISS works again!

Thanks for the help, it inspired me to find the solution.
Andrew