How can i introduce multiple conditions in LIKE operator for MS Access SQL

Question

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

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

I tried use this: https://stackoverflow.com/a/1387797/1784053 but this seems not to work in Access.

Is there way on how to achieve anything like multiple like conditioning based on dynamic set of conditions? This means, that I cant use OR neither UNION because my set of conditions is dynamic.

Similar question: How can i introduce multiple conditions in LIKE operator

How do you build the SQL statement? Where are the conditions coming from? — Thilo, Apr 23 '15 at 05:22
Basically I have something like this at the moment: `Select * from table1 t1 inner join table2 t2 on t1.column1 like (select t2.column1 where t2.column2 > 50)` — Kajiyama, Apr 23 '15 at 05:24
@Babar unfortunately not, we went with a totally different approach in the end. — Kajiyama, Aug 07 '17 at 13:10

score 2 · Answer 1 · edited Apr 23 '15 at 06:58

2

You can try this:

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

edited Apr 23 '15 at 06:58

Robert

25,425
8
67
81

answered Apr 23 '15 at 06:39

Jarvis Stark

611
5
11

As mentioned in question. I am using dynamic set and therefore i cant use neither `OR` nor `UNION`. – Kajiyama Apr 23 '15 at 08:27

cjb110 · Answer 2 · 2015-04-27T07:37:48.233

0

Would using IN be enough?

i.e.

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

You could also replace the IN clause with a select from another table.

Also you could use a VBA function:

select * from my_table where IsValidColumnName(column_name);

The IsValidColumnName would be a simple function that returns a bool if it matches whatever conditions you want.

To add the 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

Notice the Like statements changed, LIKE in VBA uses the DOS wildcards, you had SQL Server ones in your question.

edited Apr 27 '15 at 07:37

answered Apr 23 '15 at 07:18

cjb110

1,310
14
30

Nope, this won't work because I am searching for substring not whole string value. Therefor `IN` won't return what i am looking for – Kajiyama Apr 23 '15 at 08:26
then the VBA function approach looks like your best bet, as the function could do anything. – cjb110 Apr 23 '15 at 08:33
can you be more specific and provide some example please? – Kajiyama Apr 23 '15 at 10:00

score -1 · Answer 3 · answered Apr 23 '15 at 05:29

-1

Try this

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

Query

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+'%';

answered Apr 23 '15 at 05:29

SimarjeetSingh Panghlia

2,200
13
14

This ends up with: `Syntax error in CREATE TABLE statement.` – Kajiyama Apr 23 '15 at 05:33
first create a function and than execute the query – SimarjeetSingh Panghlia Apr 23 '15 at 05:34
I mean Create function statement (i ran only create function statement) threw this error – Kajiyama Apr 23 '15 at 05:35
how it is giving Syntax error in CREATE TABLE statement. because there is not a 'create table' statement in my function – SimarjeetSingh Panghlia Apr 23 '15 at 05:38
I assume it will have something to do with this: http://stackoverflow.com/a/19837233/1784053 – Kajiyama Apr 23 '15 at 05:40
sorry i dont know why my create function giving an error in your sql – SimarjeetSingh Panghlia Apr 23 '15 at 05:44
3

probably because the SQL is for SQL Server and not Access SQL. – cjb110 Apr 23 '15 at 07:11

How can i introduce multiple conditions in LIKE operator for MS Access SQL

3 Answers3