How to use a non-char list inside of an sql-LIKE wildcard?

user_16480507 0
Viewed 29

I'm trying to take the following code and make it DRY without VBA. code (ms access 2007 sql):

SELECT *
FROM Student_Enrollment
WHERE ID LIKE '*2*'
OR ID LIKE '*5*'
OR ID LIKE '*8*'
OR ID LIKE '*17*'
OR ID LIKE '*14*'
OR ID LIKE '*11*'
OR ID LIKE '*21*'

It seems reasonable to me that something like the following should be possible:

SELECT *
FROM Student_Enrollment
WHERE ID LIKE '*[2,5,8,17,14,11,21]*'

but it doesn't work. It treats each list element as a list on its own, for example, it returns entries whose id contains 1 despite 1 not being in the list above, but 11 is in there and it thinks that 11 is just 1,1... how to solve this?

ms-access-2007 wildcard sql-like sql
1 Answers

Unfortunately, you cannot do exactly what you want. But you can simplify your logic to:

SELECT *
FROM Student_Enrollment
WHERE ID LIKE "*[258]*" OR
      ID LIKE "*1[174]*" OR
      ID LIKE "*21*';

Then, if something matches "21", then it also matches "*2", so the last condition is redundant:

SELECT *
FROM Student_Enrollment
WHERE ID LIKE "*[258]*" OR
      ID LIKE "*1[174]*"
user_11440350
Related