Join fields in SQLite Query

Databases
1

I have a listbox with one column which is filled with two fields from a database (prefix and name) joined by a space.
Not all entries have a prefix eg.

Ch Joseph
Boris
Ch Benjamin

I have a textbox for Searching that is supposed to filter the list as the user types any text that may appear in a listbox entry.
I want the user to type any part of the name or prefix eg. the first entry should appear if they type “Ch Jo” or just “Josep”.

To filter the listbox the program run this query:

"Select * from myTable Where trim(prefix||' '||name) LIKE  '%" + txtSearch.Text + "%'"

The Trim part is to remove the space if there is no prefix.

For some reason it only produces half the results.

This works (below) but doesn’t produce the desired results if I type the prefix letters in because it only searches the name field.

"Select * from myTable Where name LIKE  '%" + txtSearch.Text + "%'"

Any ideas would be much appreciated. I’ve spent a lot of time on this.

2

Without seeing your data, I’d guess that you have null in some of your fields. When you concatenate null in that manner, you get null and a LIKE query will not work. Try this instead:

trim(coalesce(prefix, '' ) || ' ' || coalesce(name, ''))
3

FYI, COALESCE will take a list of parameters and return the first non-NULL value in the list, if any.

4

Once you have that resolved, try copy-and-pasting the following into your search field and see what happens:

' ; SELECT 'I could have deleted your data here' --

Use Prepared Statements.

5

[quote=381226:@Kem Tekinay]Without seeing your data, I’d guess that you have null in some of your fields. When you concatenate null in that manner, you get null and a LIKE query will not work. Try this instead:

trim(coalesce(prefix, ‘’ ) || ’ ’ || coalesce(name, ‘’))[/quote]

Spot on Kem. I was baffled why only half the data in the database showed up with my query even though two records appeared almost identical. There was null values in the prefix field on entries that were imported from another database.
Coalesce did the trick.

6

[quote=381232:@Kem Tekinay]Once you have that resolved, try copy-and-pasting the following into your search field and see what happens:

' ; SELECT 'I could have deleted your data here' --

Use Prepared Statements.[/quote]
Yes true I totally agree.
But there’s code you write for yourself and then there’s simplified code you post on forums to ask your question in the simplest possible form.
eg. I wouldn’t normally SELECT * from myTable if I only need the name and prefix fields to fill the listbox, nor would I put the listbox.text in the query, I would use a variable for the input so I could validate it first.
It just makes it easier for the reader to see where everything is coming from without distraction.