Page 1 of 1
sql apostrophe problem
Posted: Sunday 16th January 2022 10:36am
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.
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
Re: sql apostrophe problem
Posted: Sunday 16th January 2022 12:29pm
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.
Re: sql apostrophe problem
Posted: Sunday 16th January 2022 3:35pm
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
Re: sql apostrophe problem
Posted: Monday 17th January 2022 10:06am
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:-
And it works fine!
Again thanks for your ideas on this tiresome issue.