Search in multiple columns Android sqlite

Question

I need help with a search query for a android app, I'm using sqlite. Before it easy to search for a street name because i had only one column to search from, I did this to fix the sort by query check here.

i have a database with the following rows and columns:

id  street_name   street_number   sub_number 
1   PIERPONTWEG        25             
2   PIERPONTWEG        45             B     
3   PIERPONTWEG        77             
4   SARKIWEG           45             A    
5   SARKIWEG           13                 
6   SARKIWEG           5                 
7   ALIBAKSWEG         5                 
8   SHANTIWEG          44             A          
9   SHANTIWEG          44             B       
10  SHANTIWEG          44             C

I have a only one search box where i search for a street name for example 'SHANTIWEG 44' and want the following results:'SHANTIWEG 44 A','SHANTIWEG 44 B','SHANTIWEG 44 C'.

Use `SELECT * FROM YourTable WHERE Street_Name = 'SHANTIWEG' AND Street_Number = 44` — Phantômaxx, Sep 01 '14 at 12:42

score 1 · Accepted Answer · answered Sep 01 '14 at 12:47

1

You can concatenate strings (with the operator ||), but there will be no index in use.

SELECT * 
FROM TableName 
WHERE street_name||" "||street_number LIKE 'SHANTIWEG 44%'

answered Sep 01 '14 at 12:47

MaxChinni

1,116
12
20

Thanks for the answer, i get no results for that – Gilbert92 Sep 01 '14 at 12:53
@Gilbert92 maybe there's some whitespace in the values to trim. You could check with something like `SELECT street_name||"-"||street_number||"-"`, just to verify it. – MaxChinni Sep 01 '14 at 13:02
@m.chinni thanks it works, had to remove the whitespace. going to change the LIKE 'SHANTIWEG 44%' to LIKE '%SHANTIWEG 44%' – Gilbert92 Sep 01 '14 at 13:09
@Gilbert92 Maybe you should trim the values before inserting them in the table, so you keep the data normalized. – MaxChinni Sep 01 '14 at 13:17
@m.chinni will do, Thanks – Gilbert92 Sep 01 '14 at 13:35

Search in multiple columns Android sqlite

1 Answers1