How can I introduce several conditions in the LIKE statement for MS Access SQL

Question

I would like to know if there is any way how to achieve something similar in sql access.

select * from my_table where column_name like ('ABC%', 'MOP%'); 

I tried to use this: https://stackoverflow.com/a/166268/2126 , but this does not seem to work in Access.

Is there a way to achieve something like the plural, for example, conditioning based on a dynamic set of conditions? This means that I cannot use OR nor UNION because my set of conditions is dynamic.

A similar question: How can I introduce several conditions in a LIKE operator

Answer 1

You can try the following:

 select * from my_table where column_name like ('ABC%') or column_name like ('MOP%'); 

Answer 2

Is using IN enough?

i.e.

 select * from my_table where column_name in ("ABC", "MOP"); 

You can also replace the IN clause with a choice from another table.

You can also use the VBA function:

 select * from my_table where IsValidColumnName(column_name); 

IsValidColumnName will be a simple function that returns bool if it matches any conditions you want.

To add a function, create a new module, and then you can enter something like:

 Public Function IsValidColumnName(columnName As String) As Boolean If columnName Like "ABC*" Or columnName Like "MOP*" Then IsValidColumnName = True Else IsValidColumnName = False End If End Function 

Note that Like variables modified by LIKE in VBA use DOS wildcards, you had SQL Server questions in your question.

Answer 3

try it

Function

 CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20)) RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value varchar(8000) ) AS BEGIN DECLARE @index int SET @index = -1 WHILE (LEN(@text) > 0) BEGIN SET @index = CHARINDEX(@delimiter , @text) IF (@index = 0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index > 1) BEGIN INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) SET @text = RIGHT(@text, (LEN(@text) - @index)) END ELSE SET @text = RIGHT(@text, (LEN(@text) - @index)) END RETURN END 

Request

 select * from my_table inner join (select value from fn_split('ABC,MOP',',')) as split_table on my_table.column_name like '%'+split_table.value+'%';