sql apostrophe problem

Post

Posted
Rating:
#1 (In Topic #801)
bill-lancaster
Regular
bill-lancaster is in the usergroup ‘Regular’
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

PILGRIM'S PRIDE LT    NO REF             BGC

But a search containing that string with the apostrophe escaped fails

Code

hConn.Find("temp", "sMemo = '" & Replace(sVar, "'", "''") & "'")

Any ideas would be welcome
Online now: No Back to the top

Post

Posted
Rating:
#2
stevedee
Avatar
Regular
stevedee is in the usergroup ‘Regular’

bill-lancaster said

…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.
Online now: No Back to the top

Post

Posted
Rating:
#3
cogier
Avatar
Guru
cogier is in the usergroup ‘Guru’
Do you need the apostrophes? If not, why not just remove them all from the CSV file before inputting the data.

Code (gambas)

  1. Public Sub Form_Open()
  2.  
  3.   Dim sData As String[] = Split(File.Load(User.Home &/ "temp.csv"), gb.NewLine, "", True)
  4.   Dim sAps As Integer[] = [39, 96, 145, 146, 180] 'As per Steve's post
  5.  
  6.   For iLoop As Integer = 0 To sData.Max
  7.     For iAps As Integer = 0 To sAps.Max
  8.       sData[iLoop] = Replace(sData[iLoop], Chr(sAps[iAps]), "")
  9.     Next
  10.   Next
  11.  
  12.   File.Save(User.Home &/ "NewTemp.csv", sData.Join(gb.NewLine))
  13.  
Online now: No Back to the top

Post

Posted
Rating:
#4
bill-lancaster
Regular
bill-lancaster is in the usergroup ‘Regular’
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

Replace(sVar, "'", "\\'")
And it works fine!
Again thanks for your ideas on this tiresome issue.
Online now: No Back to the top
1 guest and 0 members have just viewed this.