In SQL, how can we match using LIKE up to but not exceeding a number of wildcard characters?

E.g. in regex, we can use n,m to specify that we want to match the previous element at least n times, but no more than m times.

Is there a way to do this in SQL (specifically sqlite) whereby we match a wildcard '_' up to 5 times but no more?

E.g. I want up to 5 characters between the letters j and z

So I would get Jaz or Jaaaaaz but not Jaaaaaaz

Thanks!

2 Answers
2

To answer your specific question, you could just use

WHERE col LIKE 'J%z' AND LENGTH(col) < 8


e.g.

SELECT 'Jaaaz' LIKE 'J%z' AND LENGTH('Jaaaz') < 8, 'Jaaaaaaz' LIKE 'J%z' AND LENGTH('Jaaaaaaz') < 8


Output

1 0


You can add a regexp module into SQLite. That is probably the best solution to your problem.

You can do what you want as:

where col not like '______' -- 6 underscores


Or, more simply:

when length(col) < 6


That is the direct answer to your question.

EDIT:

For up to five characters, you can do:

where col like 'j%z' and
col not like 'j______z'


Or use the more brute force:

where col like 'jz' or
col like 'j_z' or
col like 'j__z' or
col like 'j___z' or
col like 'j____z' or
col like 'j_____z'


If you want these patterns within a string (rather than the whole string), then include % at the beginning and end of the pattern.

%

not like '%______%'

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.