CODEWITHSUNDEEP
sqlite
The Matrix
The Matrix

Reputation: 1338

How to select columns that contains partially a string in SQLITE

I need to search for rows in a table and I need to select the values that starts partially with a provided value. I known how search for a pattern but I have the pattern in the table, not in the value, and I don't know if its' possible. My question is partically like a LIKE, but backward :)

Example:

If in my table I have the values 234, 1567, 31, 3145 and the provided value is 23434 I need to select 234, if the provided value is 95214 I need to return a empty select and, finally if the provided value is 314, I need to return 3145. If this possible? How? Thanks

Upvotes: 3

Views: 1207

Answers (1)

CL.
CL.

Reputation: 180070

Normally, you check a column's value against a pattern:

SELECT ... WHERE MyTable.Name LIKE 'prefix%'

However, both operands of LIKE are string expressions. You can simply put the column on the right side:

SELECT ... WHERE '23434' LIKE MyTable.Pattern

If the column value does not include the '%', you have to add it:

SELECT ... WHERE '23434' LIKE MyTable.Prefix || '%'

To get the longest match, you have to get the one that compares larger than all others, i.e., add:

... ORDER BY MyTable.Prefix DESC LIMIT 1

Upvotes: 3

Related Questions