0

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'.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Gilbert92
  • 230
  • 2
  • 10

1 Answers1

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%'
MaxChinni
  • 1,206
  • 14
  • 21
  • 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