2

I have an SQLite database attached to a Xojo project. I need to strip off the first character, IF it is a quote mark. Here is what I tried:

  t=Chr(34)+"%"  // this holds the Quote mark and the wild card  
  S="db.SQLExecute UPDATE "+ CurrentTableName + " SET " + Feilder +" = SUBSTRING("+Feilder+", 2, LEN("+Feilder+")) "
  S=S+"WHERE "+Feilder+" LIKE "+t
  db.SQLExecute S

I am getting a syntax error even without the WHERE clause. CurrentTableName holds the table name, Feilder holds the field (or column) being evaluated.

In SQLite, here is what I am trying to do:

UPDATE Table SET Myfield = SUBSTRING(Myfield, 2, LEN(MyField))
WHERE MyFiled LIKE "%

Thanks, Eric.

dda
  • 6,030
  • 2
  • 25
  • 34
EricZee
  • 31
  • 4

2 Answers2

1

Yea! Fixed it. Thanks

My OLD code had S="db.SQLExecute UPDATE ... Later I execute db.SQLExecure S

As you see from below, one change and ... it works. The S= now starts with the UPDATE and not the execute command.

BTW, this code strips off the leading quote if it gets into a database field. And ONLY the leading quote.

  t=Chr(34)+"%"
  S="UPDATE "+ CurrentTableName + " SET " + Feilder +" = SUBSTR("+Feilder+", 2, LENGTH("+Feilder+")) "
  S=S+"WHERE "+Feilder+" LIKE '"+t+"'"

  db.SQLExecute S

Thanks Kyle for the help.

EricZee
  • 31
  • 4
0

The function for selecting a substring is subst(), rather than substring(). Also, you should put single quotes around the "%...('"%').

Kyle Willoughby
  • 21
  • 3
  • That did not fix the problem, but I will see if I can break this into parts and see where the issues lies. I am sure that your SUBST() part was a help. Thanks – EricZee Sep 23 '15 at 19:14