sql apostrophe problem

Post your Gambas programming questions here.
Post Reply
bill-lancaster
Posts: 190
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

sql apostrophe problem

Post by bill-lancaster »

It's happened again! Thought I'd solved this a long time ago.
I am importing data into an sql table ("temp") from a downloaded .csv file.

There are a few records containing an apostrophe e.g.

Code: Select all

PILGRIM'S PRIDE LT    NO REF             BGC
But a search containing that string with the apostrophe escaped fails

Code: Select all

hConn.Find("temp", "sMemo = '" & Replace(sVar, "'", "''") & "'")
Any ideas would be welcome
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Re: sql apostrophe problem

Post by stevedee »

bill-lancaster wrote: Sunday 16th January 2022 10:36am ...But a search containing that string with the apostrophe escaped fails
...Any ideas would be welcome
The apostrophe can be a real pain because there are so many that look the same, but are different.

A quick look at an ASCII table and I see 5 'versions':-
dec 39
dec 96
dec 145
dec 146
dec 180

...so check the decimal or hex value.
User avatar
cogier
Site Admin
Posts: 1118
Joined: Wednesday 21st September 2016 2:22pm
Location: Guernsey, Channel Islands

Re: sql apostrophe problem

Post by cogier »

Do you need the apostrophes? If not, why not just remove them all from the CSV file before inputting the data.
Public Sub Form_Open()

  Dim sData As String[] = Split(File.Load(User.Home &/ "temp.csv"), gb.NewLine, "", True)
  Dim sAps As Integer[] = [39, 96, 145, 146, 180] 'As per Steve's post

  For iLoop As Integer = 0 To sData.Max
    For iAps As Integer = 0 To sAps.Max
      sData[iLoop] = Replace(sData[iLoop], Chr(sAps[iAps]), "")
    Next
  Next

  File.Save(User.Home &/ "NewTemp.csv", sData.Join(gb.NewLine))

End
bill-lancaster
Posts: 190
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: sql apostrophe problem

Post by bill-lancaster »

Thanks for the ideas.
I'd like to keep the string as it is.
All the characters in the string are either alphabet, space (32) or apostrophe(39) but the idea of other characters hadn't occurred to me.
Tried this:-

Code: Select all

Replace(sVar, "'", "\\'") 
And it works fine!
Again thanks for your ideas on this tiresome issue.
Post Reply