SQL WHERE expr : left(fields,numberchar)

[ElieHeloua]

test form MYSQL is ok :
"SELECT name FROM " & DBTableName & " WHERE (left(name,1)) ='C'"
but for this is not ok on sqllite-android.
message error :
cur = SQL.ExecQuery(Query)
androir.databse.sqlite.sqlexcepion:near "(":syntax error (code1):
while compiling:.....
what is equivalence for lesft(name,1)='C'

thank you for your help

 

[Mahares]

Try this using the SUBSTR function:

Dim Query As String
Dim DBTableName As String
Query="SELECT name FROM  " & DBTableName & "  WHERE SUBSTR(name,1,1) ='C'"
cur = SQL.ExecQuery(Query)

 

[ElieHeloua]

Thank you.!

 

[canalrun]

Try this using the SUBSTR function:

Query="SELECT name FROM  " & DBTableName & "  WHERE SUBSTR(name,1,1) ='C'"

Hello,
This is kind of a follow-on question…

Recently I needed to find all the Joe's in my database of names. I wrote something like:

SELECT name FROM NameTable WHERE INSTR(name, 'Joe') > 0

It came back with an error: No such function: INSTR().

"Wait a minute, it's right here in the manual!"

I ended up using something like:

SELECT name FROM NameTable WHERE name LIKE '%Joe%'

Using the LIKE operator worked, but I'm wondering why the function was not recognized?

Barry.

 

[Mahares]

Hi Barry:
If you use SQLite version Personal version 3.4.51 on your PC, you can use INSTR simialr to the way you had it in your code. It works. You will not get an error. Perhaps the native SQLite engine version used by Android has not caught up yet. Maybe Erel can shed some light on that.

 

[mangojack]

Just a side step from the above questions .. I notice in a lot of posts re SQL statements a string variable is declared ,then then variable inserted into the actual SQL query /statement ie,

Dim Query As String
Dim DBTableName As String
Query="SELECT name FROM  " & DBTableName & "  WHERE SUBSTR(name,1,1) ='C'"
cur = SQL.ExecQuery(Query)

Versus ...

cur = SQL.ExecQuery("SELECT name FROM  " & DBTableName & "  WHERE SUBSTR(name,1,1) ='C'")

Is this just the more "professional' way,is there any benifit to word statements this way , or does it come down to personal choice.

Many thanks
Cheers mj

 

[canalrun]

I notice in a lot of posts re SQL statements a string variable is declared ,then then variable inserted into the actual SQL query /statement ie,

Query="SELECT name FROM  " & DBTableName & "  WHERE SUBSTR(name,1,1) ='C'"
cur = SQL.ExecQuery(Query)

Is this just the more "professional' way,is there any benifit to word statements this way , or does it come down to personal choice.

Actually, I like to use the "2" versions of ExecQuery and ExecNonQuery since these perform escaping on string variables. For example:

SQL.ExecNonQuery2("INSERT INTO NameTable (name) VALUES (?), Array as String("Joe's Diner"))

SqlLite will complain about the single quote if you don't escape "Joe's Diner"

Barry.

 

[Mahares]

@Erel: Could you please address my post which is post#5 regarding the INSTR function. It got buried in the forum abyss.

 

[Erel]

A list with the SQLite versions is available here:
database - Version of SQLite used in Android? - Stack Overflow

INSTR was added in SQLite 3.7.15. This means that only Android 4.1+ support it.