Y a-t-il une combinaison de "LIKE" Et "IN" en SQL?
en SQL I (malheureusement) ont souvent à utiliser " LIKE
" conditions en raison de bases de données qui violent presque toutes les règles de normalisation. Je ne peux pas changer maintenant. Mais ça n'a rien à voir avec la question.
de plus, j'utilise souvent des conditions comme WHERE something in (1,1,2,3,5,8,13,21)
pour une meilleure lisibilité et flexibilité de mes instructions SQL.
est-il possible de combiner ces deux choses sans écrire des sous-sélections compliquées?
je veux quelque chose d'aussi facile que WHERE something LIKE ('bla%', '%foo%', 'batz%')
au lieu de ceci:
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
je travaille avec SQl Server et Oracle ici mais je suis intéressé si cela est possible dans N'importe quel RDBMS à tous.
21 réponses
il n'y a pas de combinaison de LIKE & IN dans SQL, encore moins dans TSQL (SQL Server) ou PLSQL (Oracle). Cela s'explique en partie par le fait que la recherche en texte intégral (FTS) est la solution de rechange recommandée.
les implémentations fts D'Oracle et de SQL Server prennent en charge le mot-clé CONTAINS, mais la syntaxe est encore légèrement différente:
Oracle:
WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0
SQL Server:
WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')
référence:
si vous voulez rendre votre déclaration facilement lisible, alors vous pouvez utiliser REGEXP_LIKE (disponible à partir de la version 10 D'Oracle).
un exemple de tableau:
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.
la syntaxe originale:
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.
et une requête à la recherche simple avec 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 ...
Je ne le recommande pas moi-même en raison du pas-si-bon performance. Je collerais avec la plusieurs COMME des prédicats. Donc les exemples étaient juste pour s'amuser.
vous êtes coincé avec le
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
sauf si vous remplissez une table temp (incluez les wild cards avec les données) et rejoignez comme ceci:
FROM YourTable y
INNER JOIN YourTempTable t On y.something LIKE t.something
essayer (en utilisant la syntaxe du serveur SQL):
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
sortie:
x
----------
abcdefg
abc
(2 row(s) affected)
x
----------
abc
abcdefg
(2 row(s) affected)
avec PostgreSQL il y a la ANY
ou ALL
forme:
WHERE col LIKE ANY( subselect )
ou
WHERE col LIKE ALL( subselect )
où le sous-select renvoie exactement une colonne de données.
je suggère d'utiliser une fonction D'utilisateur TableValue si vous souhaitez encapsuler les techniques de jointure interne ou de table de température ci-dessus. Cela lui permettrait de lire un peu plus clairement.
après utilisation de la fonction de division définie à: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
on peut écrire ce qui suit à partir d'une table que j'ai créée appelée " 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.
sorties
1 Bass 2 Pike 7 Angler 8 Walleye
une approche serait de stocker les conditions dans une table temp (ou variable table dans SQL Server) et de se joindre à cela comme ceci:
SELECT t.SomeField
FROM YourTable t
JOIN #TempTableWithConditions c ON t.something LIKE c.ConditionValue
utiliser une jonction intérieure à la place:
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
une autre solution, devrait fonctionner sur N'importe quel 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)
u peut même essayer cela
fonction
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
Requête
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+'%';
pour Sql Server, vous pouvez utiliser le SQL dynamique.
la plupart du temps dans de telles situations, vous avez le paramètre de in clause basé sur certaines données de la base de données.
l'exemple ci-dessous est un peu" forcé", mais il peut correspondre à divers cas réels trouvés dans les bases de données existantes.
supposons que vous ayez la table personnes où les noms de personnes sont stockés dans un seul champ nom de personne comme Prénom + '' + Nom De Famille. Vous devez sélectionner toutes les personnes à partir d'une liste de prénoms, stockés dans le champ NameToSelect dans le tableau NamesToSelect , plus certains critères supplémentaires (comme filtré sur le sexe, la date de naissance, etc)
vous pouvez le faire comme suit
-- @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
je me demandais aussi quelque chose comme ça. Je viens de tester en utilisant une combinaison de SUBSTRING
et IN
et c'est une solution efficace pour ce genre de problème. Essayez la requête suivante:
Select * from TB_YOUR T1 Where SUBSTRING(T1.Something, 1,3) IN ('bla', 'foo', 'batz')
j'ai une solution simple, qui fonctionne dans postgresql au moins, en utilisant like any
suivi de la liste de regex. Voici un exemple, à l'identification de certains antibiotiques dans une liste:
select *
from database.table
where lower(drug_name) like any ('{%cillin%,%cyclin%,%xacin%,%mycine%,%cephal%}')
j'ai peut-être une solution pour cela, bien qu'elle ne fonctionne que dans SQL Server 2008 autant que je sache. J'ai découvert que vous pouvez utiliser le constructeur de lignes décrit dans https://stackoverflow.com/a/7285095/894974 pour rejoindre une table fictive en utilisant une clause similaire. Cela semble plus complexe que cela ne l'est, Regardez:
SELECT [name]
,[userID]
,[name]
,[town]
,[email]
FROM usr
join (values ('hotmail'),('gmail'),('live')) as myTable(myColumn) on email like '%'+myTable.myColumn+'%'
Cela donnera à tous les utilisateurs des adresses e-mail similaires à celles fournies dans la liste. J'espère que c'est de l'utiliser à n'importe qui. Le le problème a été me tracasse un peu.
si vous utilisez MySQL la recherche la plus proche que vous pouvez obtenir est en texte intégral:
cela fonctionne pour les valeurs séparées par des virgules
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 ''%') + '%'')''
donne:
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%')
si vous voulez qu'il utilise des index, vous devez omettre le premier caractère '%'
.
dans Oracle vous pouvez utiliser une collection de la manière suivante:
WHERE EXISTS (SELECT 1
FROM TABLE(ku$_vcnt('bla%', '%foo%', 'batz%'))
WHERE something LIKE column_value)
ici j'ai utilisé un type de collection prédéfini ku$_vcnt
, mais vous pouvez déclarer votre propre comme ceci:
CREATE TYPE my_collection AS TABLE OF VARCHAR2(4000);
dans le RBDMS Oracle vous pouvez obtenir ce comportement en utilisant REGEXP_LIKE fonction.
le code suivant va tester si la chaîne trois est présente dans la liste expression un | deux | trois | quatre | cinq (dans lequel le tuyau | " symbole ou fonctionnement logique).
SELECT 'Success !!!' result
FROM dual
WHERE REGEXP_LIKE('three', 'one|two|three|four|five');
RESULT
---------------------------------
Success !!!
1 row selected.
L'expression précédente est équivalente à:
three=one OR three=two OR three=three OR three=four OR three=five
pour qu'il réussisse.
par contre, l'essai suivant est un échec.
SELECT 'Success !!!' result
FROM dual
WHERE REGEXP_LIKE('ten', 'one|two|three|four|five');
no rows selected
il existe plusieurs fonctions liées aux expressions régulières (REGEXP_*) disponibles dans Oracle depuis la version 10g. Si vous êtes un développeur Oracle et intéressé ce sujet devrait être un bon début en utilisant des Expressions régulières avec la base de données Oracle .
pas de réponse comme celle-ci:
SELECT * FROM table WHERE something LIKE ('bla% %foo% batz%')
dans oracle pas de problème.
je travaille avec SQl Server et Oracle ici, mais je suis intéressé si cela est possible dans N'importe quel RDBMS du tout.
Teradata supporte LIKE ALL/ANY
syntaxe:
TOUS chaque chaîne dans la liste.
TOUT chaîne dans la liste.┌──────────────────────────────┬────────────────────────────────────┐ │ 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%' │ └──────────────────────────────┴────────────────────────────────────┘
à partir de 2016, SQL Server inclut une STRING_SPLIT
fonction . J'utilise SQL Server v17.4 et j'ai obtenu ceci pour travailler pour moi:
DECLARE @dashboard nvarchar(50)
SET @dashboard = 'P1%,P7%'
SELECT * from Project p
JOIN STRING_SPLIT(@dashboard, ',') AS sp ON p.ProjectNumber LIKE sp.value
faire
WHERE something + '%' in ('bla', 'foo', 'batz')
OR '%' + something + '%' in ('tra', 'la', 'la')
ou
WHERE something + '%' in (select col from table where ....)