By selfawaresoup


2010-06-10 13:44:16 8 Comments

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.

Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements.

Is there any possible way to combine these two things without writing complicated sub-selects?

I want something as easy as WHERE something LIKE ('bla%', '%foo%', 'batz%') instead of this:

WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'

I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.

23 comments

@Lukasz Szozda 2018-09-10 20:20:41

I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.

Teradata supports LIKE ALL/ANY syntax:

ALL every string in the list.
ANY any string in the list.

┌──────────────────────────────┬────────────────────────────────────┐
│      THIS expression …       │ IS equivalent to this expression … │
├──────────────────────────────┼────────────────────────────────────┤
│ x LIKE ALL ('A%','%B','%C%') │ x LIKE 'A%'                        │
│                              │ AND x LIKE '%B'                    │
│                              │ AND x LIKE '%C%'                   │
│                              │                                    │
│ x LIKE ANY ('A%','%B','%C%') │ x LIKE 'A%'                        │
│                              │ OR x LIKE '%B'                     │
│                              │ OR x LIKE '%C%'                    │
└──────────────────────────────┴────────────────────────────────────┘

EDIT:

jOOQ version 3.12.0 supports that syntax:

Add synthetic [NOT] LIKE ANY and [NOT] LIKE ALL operators

A lot of times, SQL users would like to be able to combine LIKE and IN predicates, as in:

SELECT *
FROM customer
WHERE last_name [ NOT ] LIKE ANY ('A%', 'E%') [ ESCAPE '!' ]

The workaround is to manually expand the predicate to the equivalent

SELECT *
FROM customer
WHERE last_name LIKE 'A%'
OR last_name LIKE 'E%'

jOOQ could support such a synthetic predicate out of the box.

@Humayoun_Kabir 2019-06-17 07:34:29

May be you think the combination like this:

SELECT  * 
FROM    table t INNER JOIN
(
  SELECT * FROM (VALUES('bla'),('foo'),('batz')) AS list(col)
) l ON t.column  LIKE '%'+l.Col+'%'

If you have defined full text index for your target table then you may use this alternative:

SELECT  * 
FROM    table t
WHERE CONTAINS(t.column, '"bla*" OR "foo*" OR "batz*"')

@Piyush Verma 2018-12-04 16:35:18

In Teradata you can use LIKE ANY ('%ABC%','%PQR%','%XYZ%'). Below is an example which has produced the same results for me

--===========
--  CHECK ONE
--===========
SELECT *
FROM Random_Table A
WHERE (Lower(A.TRAN_1_DSC) LIKE ('%american%express%centurion%bank%')
OR Lower(A.TRAN_1_DSC) LIKE ('%bofi%federal%bank%')
OR Lower(A.TRAN_1_DSC) LIKE ('%american%express%bank%fsb%'))

;
--===========
--  CHECK TWO
--===========
SELECT *
FROM Random_Table  A
WHERE Lower(A.TRAN_1_DSC) LIKE ANY 
('%american%express%centurion%bank%',
'%bofi%federal%bank%',
'%american%express%bank%fsb%')

@Mark 2018-06-07 20:33:25

Starting with 2016, SQL Server includes a STRING_SPLIT function. I'm using SQL Server v17.4 and I got this to work for me:

DECLARE @dashboard nvarchar(50)
SET @dashboard = 'P1%,P7%'

SELECT * from Project p
JOIN STRING_SPLIT(@dashboard, ',') AS sp ON p.ProjectNumber LIKE sp.value

@Hong Van Vit 2017-11-20 05:35:48

No answer like this:

SELECT * FROM table WHERE something LIKE ('bla% %foo% batz%')

In oracle no problem.

@Fandango68 2018-07-05 01:22:07

Doesn't work but +1 for trying

@Sander 2013-08-21 12:43:32

I may have a solution for this, although it will only work in SQL Server 2008 as far as I know. I discovered that you can use the row-constructor described in https://stackoverflow.com/a/7285095/894974 to join a 'fictional' table using a like clause. It sounds more complex then it is, look:

SELECT [name]
  ,[userID]
  ,[name]
  ,[town]
  ,[email]
FROM usr
join (values ('hotmail'),('gmail'),('live')) as myTable(myColumn) on email like '%'+myTable.myColumn+'%' 

This will result in all users with an e-mail adres like the ones provided in the list. Hope it's of use to anyone. The problem had been bothering me a while.

@HLGEM 2013-08-21 12:50:13

That's interesting. However, be aware that this should only be used on a smal table as the like statement can't use indexes. This is why the full text search, while harder to intially set up, is the better choice if you have alot of data.

@mkomo 2016-12-28 19:13:35

I have a simple solution, that works in postgresql at least, using like any followed by the list of regex. Here is an example, looking at identifying some antibiotics in a list:

select *
from database.table
where lower(drug_name) like any ('{%cillin%,%cyclin%,%xacin%,%mycine%,%cephal%}')

@jackattack 2016-12-26 19:49:53

In Oracle RBDMS you can achieve this behavior using REGEXP_LIKE function.

The following code will test if the string three is present in the list expression one|two|three|four|five (in which the pipe "|" symbol means OR logic operation).

SELECT 'Success !!!' result
FROM dual
WHERE REGEXP_LIKE('three', 'one|two|three|four|five');

RESULT
---------------------------------
Success !!!

1 row selected.

Preceding expression is equivalent to:

three=one OR three=two OR three=three OR three=four OR three=five

So it will succeed.

On the other hand, the following test will fail.

SELECT 'Success !!!' result
FROM dual
WHERE REGEXP_LIKE('ten', 'one|two|three|four|five');

no rows selected

There are several functions related to regular expressions (REGEXP_*) available in Oracle since 10g version. If you are an Oracle developer and interested this topic this should be a good beginning Using Regular Expressions with Oracle Database.

@mik 2016-10-18 09:54:01

Another solution, should work on any RDBMS:

WHERE EXISTS (SELECT 1
                FROM (SELECT 'bla%' pattern FROM dual UNION ALL
                      SELECT '%foo%'        FROM dual UNION ALL
                      SELECT 'batz%'        FROM dual)
               WHERE something LIKE pattern)

@Fandango68 2018-07-05 01:23:26

But it's uglier than a set of OR statements

@mik 2018-08-06 12:48:50

@Fandango68, but the union of selects can be replaced by another source of patterns like a table, a view, etc.

@Fandango68 2018-08-07 02:21:15

@mik true. Didn't think of other possibilities.

@mik 2016-05-16 14:48:55

In Oracle you can use a collection in the following way:

WHERE EXISTS (SELECT 1
                FROM TABLE(ku$_vcnt('bla%', '%foo%', 'batz%'))
               WHERE something LIKE column_value)

Here I have used a predefined collection type ku$_vcnt, but you can declare your own one like this:

CREATE TYPE my_collection AS TABLE OF VARCHAR2(4000);

@ssah 2016-02-19 20:39:55

I was also wondering for something like that. I just tested using a combination of SUBSTRING and IN and it is an effective solution for this kind of problem. Try the below query :

Select * from TB_YOUR T1 Where SUBSTRING(T1.Something, 1,3) IN ('bla', 'foo', 'batz')

@ShoeLace 2016-11-02 16:40:00

one issue with this approach is you loose the ability to use an index on t1.something if it exists..

@virsha 2017-11-03 15:18:35

Thanks, it works like charm for me

@Nikolay Hristov 2015-04-17 14:08:58

do this

WHERE something + '%' in ('bla', 'foo', 'batz')
OR '%' + something + '%' in ('tra', 'la', 'la')

or

WHERE something + '%' in (select col from table where ....)

@Darius X. 2015-04-17 14:14:16

How's that going to work? The LHS is a string with a %, and that % is therefore not a wildcard

@bzamfir 2015-06-15 14:42:17

For Sql Server you can resort to Dynamic SQL.

Most of the time in such situations you have the parameter of IN clause based on some data from database.

The example below is a little "forced", but this can match various real cases found in legacy databases.

Suppose you have table Persons where person names are stored in a single field PersonName as FirstName + ' ' + LastName. You need to select all persons from a list of first names, stored in field NameToSelect in table NamesToSelect, plus some additional criteria (like filtered on gender, birth date, etc)

You can do it as follows

-- @gender is nchar(1), @birthDate is date 

declare 
  @sql nvarchar(MAX),
  @subWhere nvarchar(MAX)
  @params nvarchar(MAX)

-- prepare the where sub-clause to cover LIKE IN (...)
-- it will actually generate where clause PersonName Like 'param1%' or PersonName Like 'param2%' or ...   
set @subWhere = STUFF(
  (
    SELECT ' OR PersonName like ''' + [NameToSelect] + '%''' 
        FROM [NamesToSelect] t FOR XML PATH('')
  ), 1, 4, '')

-- create the dynamic SQL
set @sql ='select 
      PersonName
      ,Gender
      ,BirstDate    -- and other field here         
  from [Persons]
  where 
    Gender = @gender
    AND BirthDate = @birthDate
    AND (' + @subWhere + ')'

set @params = ' @gender nchar(1),
  @birthDate Date'     

EXECUTE sp_executesql @sql, @params,    
  @gender,  
  @birthDate

@SimarjeetSingh Panghlia 2015-04-23 11:37:30

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

@David F Mayer 2014-08-19 12:41:35

This works for comma separated values

DECLARE @ARC_CHECKNUM VARCHAR(MAX)
SET @ARC_CHECKNUM = 'ABC,135,MED,ASFSDFSF,AXX'
SELECT ' AND (a.arc_checknum LIKE ''%' + REPLACE(@arc_checknum,',','%'' OR a.arc_checknum LIKE ''%') + '%'')''

Evaluates to:

 AND (a.arc_checknum LIKE '%ABC%' OR a.arc_checknum LIKE '%135%' OR a.arc_checknum LIKE '%MED%' OR a.arc_checknum LIKE '%ASFSDFSF%' OR a.arc_checknum LIKE '%AXX%')

If you want it to use indexes, you must omit the first '%' character.

@OMG Ponies 2010-06-10 17:34:03

There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.

Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:

Oracle:

WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0

SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')

Reference:

@Pierre-Gilles Levallois 2012-08-21 13:49:49

Hi, with Oracle, you need to build plaintext indexes on the columns you want to apply "CONTAINS" operator. Depending of your data volume this could be quite long.

@Marcel 2013-02-18 08:46:51

With SQL Server (at least the 2008 version) the comment of @Pilooz does apply too, you need to build full text indexes.

@Rob van Wijk 2010-06-10 14:19:20

If you want to make your statement easily readable, then you can use REGEXP_LIKE (available from Oracle version 10 onwards).

An example table:

SQL> create table mytable (something)
  2  as
  3  select 'blabla' from dual union all
  4  select 'notbla' from dual union all
  5  select 'ofooof' from dual union all
  6  select 'ofofof' from dual union all
  7  select 'batzzz' from dual
  8  /

Table created.

The original syntax:

SQL> select something
  2    from mytable
  3   where something like 'bla%'
  4      or something like '%foo%'
  5      or something like 'batz%'
  6  /

SOMETH
------
blabla
ofooof
batzzz

3 rows selected.

And a simple looking query with REGEXP_LIKE

SQL> select something
  2    from mytable
  3   where regexp_like (something,'^bla|foo|^batz')
  4  /

SOMETH
------
blabla
ofooof
batzzz

3 rows selected.

BUT ...

I would not recommend it myself due to the not-so-good performance. I'd stick with the several LIKE predicates. So the examples were just for fun.

@DCookie 2010-06-10 14:36:38

+1 nice illustration of REGEXP usage in 10g. I'm curious, though, if performance would really be all that much worse. Both will require full table and/or index scans, no?

@Rob van Wijk 2010-06-10 14:47:32

True. But regular expressions burn CPU like crazy, not I/O. If it is worse and how much worse it is, depends on how large your list of expressions is and whether the column is indexed or not, among others. It is just a warning, so that the original poster is not surprised when he starts implementing it.

@chris 2013-07-09 14:10:18

If you are using MySQL the closest you can get is full-text search:

Full-Text Search, MySQL Documentation

@Benoit 2011-04-21 09:17:35

With PostgreSQL there is the ANY or ALL form:

WHERE col LIKE ANY( subselect )

or

WHERE col LIKE ALL( subselect )

where the subselect returns exactly one column of data.

@Assad Ebrahim 2015-11-07 11:37:35

Are LIKE ANY and LIKE ALL common to all SQL dialects, i.e. part of the core language, or specific to a dialect?

@Benoit 2015-11-10 09:52:21

@AssadEbrahim, no they are specific. Oracle has = ANY or <> ALL but it works only in SQL, not in PLSQL for example.

@ypercubeᵀᴹ 2017-03-15 09:12:26

I think this is standard syntax (but not many DBMS have implemented it)

@rogerdpack 2018-09-26 17:31:43

@Famous Nerd 2010-06-10 17:18:17

I would suggest using a TableValue user function if you'd like to encapsulate the Inner Join or temp table techniques shown above. This would allow it to read a bit more clearly.

After using the split function defined at: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

we can write the following based on a table I created called "Fish" (int id, varchar(50) Name)

SELECT Fish.* from Fish 
    JOIN dbo.Split('%ass,%e%',',') as Splits 
    on Name like Splits.items  //items is the name of the output column from the split function.

Outputs

1   Bass
2   Pike
7   Angler
8   Walleye

@Famous Nerd 2010-06-10 17:37:55

That output looks much better. Thanks kindly.

@mik 2018-05-22 16:14:40

A row will be duplicated if matched by many conditions at once.

@A-K 2010-06-10 16:35:43

Use an inner join instead:

SELECT ...
FROM SomeTable
JOIN
(SELECT 'bla%' AS Pattern 
UNION ALL SELECT '%foo%'
UNION ALL SELECT 'batz%'
UNION ALL SELECT 'abc'
) AS Patterns
ON SomeTable.SomeColumn LIKE Patterns.Pattern

@selfawaresoup 2010-06-10 17:02:44

Well, that's exactly what I'd like to avoid. Although it works.

@Phil Factor 2014-02-20 16:30:29

Why avoid this solution? It works as fast as the accepted solution, and is just as versatile.

@Jakub Kania 2015-08-19 16:17:49

@PhilFactor This solution can create duplicate rows.

@KM. 2010-06-10 13:48:24

you're stuck with the

WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'

unless you populate a temp table (include the wild cards in with the data) and join like this:

FROM YourTable                y
    INNER JOIN YourTempTable  t On y.something LIKE t.something

try it out (using SQL Server syntax):

declare @x table (x varchar(10))
declare @y table (y varchar(10))

insert @x values ('abcdefg')
insert @x values ('abc')
insert @x values ('mnop')

insert @y values ('%abc%')
insert @y values ('%b%')

select distinct *
FROM @x x
WHERE x.x LIKE '%abc%' 
   or x.x LIKE '%b%'


select distinct x.*  
FROM @x             x
    INNER JOIN  @y  y On x.x LIKE y.y

OUTPUT:

x
----------
abcdefg
abc

(2 row(s) affected)

x
----------
abc
abcdefg

(2 row(s) affected)

@selfawaresoup 2010-06-10 13:52:21

Ok, this would work, but it's not going into my intended direction of making the SQL statement more easily readable :)

@KM. 2010-06-10 13:57:20

in SQL you go for index usage and performance. Only use indenting and naming for SQL readability, when you make other modifications for readability only you risk changing the execution plan ( which affects index usage and performance). If you are not careful, you can easily change an instantly running query to a very slow one by making trivial changes.

@Philip Kelley 2010-06-10 13:57:53

The first statement of this answer is key -- (most?) SQL-based systems and languages don't support what you want, not without implementing work-arounds. (In SQL server, would Full Text indexing help?)

@KM. 2010-06-10 14:00:44

@Philip Kelley, can SQL Server's Full Text indexing do LIKE 'bla%' , which in the OP's example code? or can in only do LIKE '%bla%' searches?

@Philip Kelley 2010-06-10 16:48:32

I honestly don't know, I've never used FT indexing. I tossed it in as a sample of a possible work-around that's already included in the product. For what he's doing (A or B or C), I suspect it doesn't do it, am fairly confident that it'd take a lot of effort to determine this, and know that its outside the scope of his original question (does SQL do it natively).

@RichardTheKiwi 2011-01-23 11:53:51

+1 for joining to table, but don't use temp, just derive it (select A union all select B... )

@DeeArgee 2016-02-11 15:06:53

The inner join and like is a nice trick, I've used inner join sometable st on st.column like '%' + t.othercolumn + '%'

@mik 2018-05-22 16:16:00

Inner join will duplicate rows that match many conditions at once.

@AdaTheDev 2010-06-10 13:48:48

One approach would be to store the conditions in a temp table (or table variable in SQL Server) and join to that like this:

SELECT t.SomeField
FROM YourTable t
   JOIN #TempTableWithConditions c ON t.something LIKE c.ConditionValue

@mik 2018-05-22 16:12:13

A row will be duplicated if matched by many conditions at once.

Related Questions

Sponsored Content

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

12 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

29 Answered Questions

37 Answered Questions

25 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

24 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

16 Answered Questions

[SOLVED] How can I do an UPDATE statement with JOIN in SQL?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

Sponsored Content