SQLite wildcard % with space

Question

How can i concatenate 2 LIKE operator as 1 for below query ?

select * from students where name LIKE 'abc%' OR name like '% abc%'

Please notice " " space after % wildcard in second like operator before 'abc'.

SQLite3 does support `REGEXP` though it requires a user defined function, which is not possible on Android. Hence, your current query is probably the best you can do. — Tim Biegeleisen, Feb 17 '17 at 15:30

score 4 · Answer 1 · answered Feb 17 '17 at 15:36

4

Just ensure that all strings have that space:

SELECT * FROM students WHERE ' ' || name LIKE '% abc%';

answered Feb 17 '17 at 15:36

CL.

173,858
17
217
259

Yes, I was too narrowly focused on the question as asked. This is a better answer from a practical standpoint. – Mark Adelsberger Feb 17 '17 at 15:56

score 1 · Accepted Answer · answered Feb 17 '17 at 15:16

1

For most databases, the LIKE operator has pretty limited pattern matching (especially if you're used to regex-type pattern matching). SQLite is, as far as I can tell, one that fits that description. No one LIKE pattern can do what you describe.

answered Feb 17 '17 at 15:16

Mark Adelsberger

42,148
4
35
52

SQLite3 supports `REGEXP` which _can_ handle the OP's logic in one expression, though sadly this isn't possible on Android which does not support user defined functions. – Tim Biegeleisen Feb 17 '17 at 15:31

SQLite wildcard % with space

2 Answers2