How do I use SQL UPDATE in Xojo to remove leading quotes

Question

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.

Answer 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.

Answer 2

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