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.
This question is related to
sql
sql-server
oracle
tsql
plsql
do this
WHERE something + '%' in ('bla', 'foo', 'batz')
OR '%' + something + '%' in ('tra', 'la', 'la')
or
WHERE something + '%' in (select col from table where ....)
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+'%';
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)
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
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%')
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.
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*"')
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')
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
I know this is very late, but I had a similar situation. I needed a "Like In" operator for a set of stored procedures I have, which accept many parameters and then uses those parameters to aggregate data from multiple RDBMS systems, thus no RDBMS-specific tricks would work, however the stored procedure and any functions will run on MS SQL Server, so we can use T-SQL for the functionality of generating the full SQL statements for each RDBMS, but the output needs to be fairly RDBMS-independent.
This is what I've come up with for the moment to turn a delimited string (such as a parameter coming into a stored procedure) into a block of SQL. I call it "Lichen" for "LIKE IN". Get it?
Lichen.sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================
-- Lichen - Scalar Valued Function
-- Returns nvarchar(512) of "LIKE IN" results. See further documentation.
-- CREATOR: Norman David Cooke
-- CREATED: 2020-02-05
-- UPDATED:
-- =======================================================================
CREATE OR ALTER FUNCTION Lichen
(
-- Add the parameters for the function here
@leadingAnd bit = 1,
@delimiter nchar(1) = ';',
@colIdentifier nvarchar(64),
@argString nvarchar(256)
)
RETURNS nvarchar(512)
AS
BEGIN
-- Declare the return variable here
DECLARE @result nvarchar(512)
-- set delimiter to detect (add more here to detect a delimiter if one isn't provided)
DECLARE @delimit nchar(1) = ';'
IF NOT @delimiter = @delimit
SET @delimit = @delimiter
-- check to see if we have any delimiters in the input pattern
IF CHARINDEX(@delimit, @argString) > 1 -- check for the like in delimiter
BEGIN -- begin 'like in' branch having found a delimiter
-- set up a table variable and string_split the provided pattern into it.
DECLARE @lichenTable TABLE ([id] [int] IDENTITY(1,1) NOT NULL, line NVARCHAR(32))
INSERT INTO @lichenTable SELECT * FROM STRING_SPLIT(@argString, ';')
-- setup loop iterators and determine how many rows were inserted into lichen table
DECLARE @loopCount int = 1
DECLARE @lineCount int
SELECT @lineCount = COUNT(*) from @lichenTable
-- select the temp table (to see whats inside for debug)
--select * from @lichenTable
-- BEGIN AND wrapper block for 'LIKE IN' if bit is set
IF @leadingAnd = 1
SET @result = ' AND ('
ELSE
SET @result = ' ('
-- loop through temp table to build multiple "LIKE 'x' OR" blocks inside the outer AND wrapper block
WHILE ((@loopCount IS NOT NULL) AND (@loopCount <= @lineCount))
BEGIN -- begin loop through @lichenTable
IF (@loopcount = 1) -- the first loop does not get the OR in front
SELECT @result = CONCAT(@result, ' ', @colIdentifier, ' LIKE ''', line, '''') FROM @lichenTable WHERE id = @loopCount
ELSE -- but all subsequent loops do
SELECT @result = CONCAT(@result, ' OR ', @colIdentifier, ' LIKE ''', line, '''') FROM @lichenTable WHERE id = @loopCount
SET @loopcount = @loopCount + 1 -- increment loop
END -- end loop through @lichenTable
-- set final parens after lichenTable loop
SET @result = CONCAT(@result, ' )')
END -- end 'like in' branch having found a delimiter
ELSE -- no delimiter was provided
BEGIN -- begin "no delimiter found" branch
IF @leadingAnd = 1
SET @result = CONCAT(' AND ', @colIdentifier, ' LIKE ''' + @argString + '''')
ELSE
SET @result = CONCAT(' ', @colIdentifier, ' LIKE ''' + @argString + '''')
END -- end "no delimiter found" branch
-- Return the result of the function
RETURN @result
END -- end lichen function
GO
The delimiter detection is possibly planned, but for now it defaults to a semicolon so you can just put default in there. There are probably bugs in this. The @leadingAnd parameter is just a bit value to determine if you want a leading "AND" put in front of the block so it fits in nicely with other WHERE clause additions.
Example Usage (with delimiter in argString)
SELECT [dbo].[Lichen] (
default -- @leadingAND, bit, default: 1
,default -- @delimiter, nchar(1), default: ';'
,'foo.bar' -- @colIdentifier, nvarchar(64), this is the column identifier
,'01%;02%;%03%' -- @argString, nvarchar(256), this is the input string to parse "LIKE IN" from
)
GO
Will return a nvarchar(512) containing:
AND ( foo.bar LIKE '01%' OR foo.bar LIKE '02%' OR foo.bar LIKE '%03%' )
It will also skip the block if the input does not contain a delimiter:
Example Usage (without delimiter in argString)
SELECT [dbo].[Lichen] (
default -- @leadingAND, bit, default: 1
,default -- @delimiter, nchar(1), default: ';'
,'foo.bar' -- @colIdentifier, nvarchar(64), this is the column identifier
,'01%' -- @argString, nvarchar(256), this is the input string to parse "LIKE IN" from
)
GO
Will return a nvarchar(512) containing:
AND foo.bar LIKE '01%'
I'm going to continue work on this, so if I've overlooked something (glaringly obvious or otherwise) please feel free to comment or reach out.
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.
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
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)
The inner select can be replaced by another source of patterns like a table (or a view) in this way:
WHERE EXISTS (SELECT 1
FROM table_of_patterns t
WHERE something LIKE t.pattern)
table_of_patterns should contain at least a column pattern, and can be populated like this:
INSERT INTO table_of_patterns(pattern) VALUES ('bla%');
INSERT INTO table_of_patterns(pattern) VALUES ('%foo%');
INSERT INTO table_of_patterns(pattern) VALUES ('batz%');
If you are using MySQL the closest you can get is full-text search:
Sorry for dredging up an old post, but it has a lot of views. I faced a similar problem this week and came up with this pattern:
declare @example table ( sampletext varchar( 50 ) );
insert @example values
( 'The quick brown fox jumped over the lazy dog.' ),
( 'Ask not what your country can do for you.' ),
( 'Cupcakes are the new hotness.' );
declare @filter table ( searchtext varchar( 50 ) );
insert @filter values
( 'lazy' ),
( 'hotness' ),
( 'cupcakes' );
-- Expect to get rows 1 and 3, but no duplication from Cupcakes and Hotness
select *
from @example e
where exists ( select * from @filter f where e.sampletext like '%' + searchtext + '%' )
Exists() works a little better than join, IMO, because it just tests each record in the set, but doesn't cause duplication if there are multiple matches.
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.
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
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%' ¦ +-------------------------------------------------------------------+
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.
PostgreSQL LIKE/ILIKE ANY (ARRAY[]):
SELECT *
FROM t
WHERE c LIKE ANY (ARRAY['A%', '%B']);
SELECT *
FROM t
WHERE c LIKE ANY ('{"Do%", "%at"}');
Snowflake also supports LIKE ANY/LIKE ALL matching:
LIKE ANY/ALL
Allows case-sensitive matching of strings based on comparison with one or more patterns.
<subject> LIKE ANY (<pattern1> [, <pattern2> ... ] ) [ ESCAPE <escape_char> ]
Example:
SELECT *
FROM like_example
WHERE subject LIKE ANY ('%Jo%oe%','T%e')
-- WHERE subject LIKE ALL ('%Jo%oe%','J%e')
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%}')
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);
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
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.
No answer like this:
SELECT * FROM table WHERE something LIKE ('bla% %foo% batz%')
In oracle no problem.
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.
Source: Stackoverflow.com