SQL WHERE expr : left(fields,numberchar)

ElieHeloua

Member
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
Try this using the SUBSTR function:
B4X:
Dim Query As String
Dim DBTableName As String
Query="SELECT name FROM  " & DBTableName & "  WHERE SUBSTR(name,1,1) ='C'"
cur = SQL.ExecQuery(Query)
 
Upvote 0

canalrun

Well-Known Member
Licensed User
Longtime User
Try this using the SUBSTR function:
B4X:
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:

B4X:
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:

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

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

Barry.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
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.
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
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,
B4X:
Dim Query As String
Dim DBTableName As String
Query="SELECT name FROM  " & DBTableName & "  WHERE SUBSTR(name,1,1) ='C'"
cur = SQL.ExecQuery(Query)

Versus ...
B4X:
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
 
Upvote 0

canalrun

Well-Known Member
Licensed User
Longtime User
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,
B4X:
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:

B4X:
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.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
@Erel: Could you please address my post which is post#5 regarding the INSTR function. It got buried in the forum abyss.
 
Upvote 0
Top