CODEWITHSUNDEEP
sqlsqliteselect
Gambit King
Gambit King

Reputation: 483

Select statement with offset in like

I have an entry "123456789" in my table. 

Select * from map where col like '%1%5%6%7%9'.

I want to retrieve the records where the order of the input sequence matches, but i also want to ensure that the distance between any 2 matching digits is less than 2.

Is there any way i can specify an offset ?

enter image description here

The input is 189, and it selects the record, but i want 1.8.9 to be within 2 of each other. 12879 would be an acceptable output but 123456789 would not be.

Upvotes: 0

Views: 90

Answers (1)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

Below statement requires 3 to 5 characters between 1 and 5:

SELECT * FROM map WHERE col LIKE '%1___%5%6%7%9' AND col NOT LIKE '%1______%5%6%7%9%'

Using _s you may force any count of characters.

EDIT: Character corrected. Source: SQLite expression

Check in this SQL Fiddle sample.

Upvotes: 1

Related Questions