DarkMann Member Licensed User Longtime User Dec 6, 2011 #1 I'm working on my first App that uses a SQLite database and all seems to be going quite well. I can read the database into my app, change data within records and make sure the changes are stored, all with no problem. Now, as part of the major functionality of the program, I need to be able to filter my list of data and display only part of it. I have no problem with the display of the list, but I just don't know enough SQL to figure out how to return a set of results that contain the string (or strings) typed into the filter. my basic select - to get all records looks like this: Code: Copy SELECT _id,Common,Scientific,Seen FROM t_birds and this works just fine. I need to look for a partial string within the Common column and return the results. Code: Copy SELECT _id,Common,Scientific,Seen FROM t_birds WHERE Common **SOME STRING** I have tried loads of stuff using what I can find around the net, but nothing actually gives me a new list. Any suggestions much appreciated. David
I'm working on my first App that uses a SQLite database and all seems to be going quite well. I can read the database into my app, change data within records and make sure the changes are stored, all with no problem. Now, as part of the major functionality of the program, I need to be able to filter my list of data and display only part of it. I have no problem with the display of the list, but I just don't know enough SQL to figure out how to return a set of results that contain the string (or strings) typed into the filter. my basic select - to get all records looks like this: Code: Copy SELECT _id,Common,Scientific,Seen FROM t_birds and this works just fine. I need to look for a partial string within the Common column and return the results. Code: Copy SELECT _id,Common,Scientific,Seen FROM t_birds WHERE Common **SOME STRING** I have tried loads of stuff using what I can find around the net, but nothing actually gives me a new list. Any suggestions much appreciated. David
D devlei Active Member Licensed User Longtime User Dec 6, 2011 #2 Try the following: Code: Copy "SELECT _id, Common, Scientific, Seen FROM t_birds WHERE Common='Some String'" or if your Some String is in a variable (SomeString) Code: Copy "SELECT _id, Common, Scientific, Seen FROM t_birds WHERE Common='" & SomeString & "'" Note: The single quotes (') identify the string. Dave Upvote 0
Try the following: Code: Copy "SELECT _id, Common, Scientific, Seen FROM t_birds WHERE Common='Some String'" or if your Some String is in a variable (SomeString) Code: Copy "SELECT _id, Common, Scientific, Seen FROM t_birds WHERE Common='" & SomeString & "'" Note: The single quotes (') identify the string. Dave
Erel B4X founder Staff member Licensed User Longtime User Dec 6, 2011 #3 You should use LIKE: Code: Copy SELECT _id, Common, Scientific, Seen FROM t_birds WHERE Common LIKE '%Some String%'" Upvote 0
You should use LIKE: Code: Copy SELECT _id, Common, Scientific, Seen FROM t_birds WHERE Common LIKE '%Some String%'"
klaus Expert Licensed User Longtime User Dec 6, 2011 #4 You could have a look at chapter 12.4 Database example in the Beginner's Guide. Best regads. Upvote 0
DarkMann Member Licensed User Longtime User Dec 6, 2011 #5 Hi, Thanks to all for the hints. I am at work at present, but will report back later. David Upvote 0
DarkMann Member Licensed User Longtime User Dec 6, 2011 #6 Hi again, Just to follow up to say thanks once more. Using LIKE with single quotes around the wildcard %s and the required string as Erel suggests works fine. Don't know why I didn't use SQL before now, as it saves a lot of effort. Should have some work to show now in the next few days. David Upvote 0
Hi again, Just to follow up to say thanks once more. Using LIKE with single quotes around the wildcard %s and the required string as Erel suggests works fine. Don't know why I didn't use SQL before now, as it saves a lot of effort. Should have some work to show now in the next few days. David
T timo Active Member Licensed User Longtime User Jan 11, 2012 #7 I've found this thread while searching for something else. I just wanted to add that I've had some problems using LIKE v/s '=' with strings in SqLite. It seems that LIKE 'string' is not allways case sensitive (but = 'string' yes). I'm anyway not shure about that, but it's what i encountered. Upvote 0
I've found this thread while searching for something else. I just wanted to add that I've had some problems using LIKE v/s '=' with strings in SqLite. It seems that LIKE 'string' is not allways case sensitive (but = 'string' yes). I'm anyway not shure about that, but it's what i encountered.
