SQL SELECT from table when substring is in a string

Question

Let's say I have a table named table1 in a sqlite3 database (via python) with two columns named 'id' and 'string' and here are the three entries in the table:

   id                string
  'a'               'google'
  'b'               'apple'
  'c'            'application'

so let's say I want all the rows with 'app' in them, so how would i do that? I'm looking for something like this:

SELECT * FROM table1 WHERE 'app' in string

which would in theory return the last 2 rows....

Anish Shah · Accepted Answer · 2014-04-06T06:52:49.937

0

You can use LIKE

SELECT * FROM table1 
WHERE string LIKE '%app%'

DEMO

UPDATE:

When you use a front wildcard (LIKE '%app%'), the performance will be roughly the same.

If you want string starting with app, then using LIKE 'app%' would be better. Here's the reason :-

The part before the first wildcard serves as an access predicate, that is, they limit the scanned index range. The remaining characters do not narrow the scanned index range—non-matching entries are just left out of the result.

edited Apr 06 '14 at 06:52

answered Apr 06 '14 at 06:23

Anish Shah

7,669
8
29
40

but i've heard that like is slow.. how can i make it fast? – Anshu Dwibhashi Apr 06 '14 at 06:24
@AnshumanDwibhashi - Take a look at http://stackoverflow.com/questions/2451608/which-is-faster-instr-or-like – Aiias Apr 06 '14 at 06:36
@AnshumanDwibhashi Does all the value in field starts with 'app' or it can be anywhere in the string – Anish Shah Apr 06 '14 at 06:39

score 0 · Answer 2 · answered Apr 06 '14 at 06:24

0

Try this:

SELECT * FROM table1 WHERE string LIKE '%app%'

answered Apr 06 '14 at 06:24

Raging Bull

18,593
13
50
55

score 0 · Answer 3 · answered Apr 06 '14 at 06:31

It's very simple. You need to use the LIKE '%' operator in SQL. It's for string matching.

Your query should be something like this:

SELECT * FROM table1
WHERE string LIKE '%app%'

Since you didn't mention whether 'app' should be positioned at the beginning of string or the end, I've wrapped the string 'app' around two '%', so that it will locate the 'app' string anywhere inside string.

SQL SELECT from table when substring is in a string

3 Answers3