在SQL I中(很遗憾)经常不得不使用“LIKE”条件，因为数据库违反了几乎所有的规范化规则。我现在改变不了。但这与问题无关。
此外，我经常使用诸如WHERE(1,1,2,3,5,8,13,21)中的某些内容之类的条件，以提高SQL语句的可读性和灵活性。
有没有可能在不编写复杂的子选择的情况下将这两者结合起来?
我想要一些像WHERE一样简单的东西('bla%'， '%foo%'， 'batz%')而不是这样:
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
我在这里与SQl Server和Oracle一起工作，但我感兴趣的是，这是否可能在任何RDBMS中。
当前回答
在Teradata你可以使用LIKE ANY ('%ABC%'，'%PQR%'，'%XYZ%')。下面是一个对我来说产生了相同结果的例子
--===========
-- 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%')
其他回答
你甚至可以试试这个
函数
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
查询
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+'%';
您可以以牺牲性能为代价，为大量元素动态地执行此操作，但这是可行的。
DECLARE @val nvarchar(256),
@list nvarchar(max) = 'one,two,three,ten,five';
CREATE table #table (FIRST_NAME nvarchar(512), LAST_NAME nvarchar(512));
CREATE table #student (FIRST_NAME nvarchar(512), LAST_NAME nvarchar(512), EMAIL
nvarchar(512));
INSERT INTO #student (FIRST_NAME, LAST_NAME, EMAIL)
SELECT 'TEST', ' redOne' ,'test.redOne@toto.com' UNION ALL
SELECT 'student', ' student' ,'student@toto.com' UNION ALL
SELECT 'student', ' two' ,'student.two@toto.com' UNION ALL
SELECT 'hello', ' ONE TWO THREE' ,'student.two@toto.com'
DECLARE check_cursor CURSOR FOR select value from STRING_SPLIT(@list,',')
OPEN check_cursor
FETCH NEXT FROM check_cursor INTO @val
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @val
IF EXISTS (select * from #student where REPLACE(FIRST_NAME, ' ','')
like '%' + @val + '%' OR REPLACE(LAST_NAME, ' ','') like '%' + @val + '%')
BEGIN
INSERT INTO #table (FIRST_NAME, LAST_NAME )
SELECT TOP 1 FIRST_NAME, LAST_NAME VALUE from #student where
REPLACE(FIRST_NAME, ' ','') like '%' + @val + '%' OR REPLACE(LAST_NAME, ' ','')
like '%' + @val + '%'
END;
FETCH NEXT FROM check_cursor INTO @val
END
CLOSE check_cursor;
DEALLOCATE check_cursor;
SELECT * FROM #table;
DROP TABLE #table;
DROP TABLE #student;
我有一个简单的解决方案，至少在postgresql中工作，使用like any后跟regex列表。这里有一个例子，看看在清单中识别一些抗生素:
select *
from database.table
where lower(drug_name) like any ('{%cillin%,%cyclin%,%xacin%,%mycine%,%cephal%}')
在SQL SERVER中使用游标并执行每一个值:
表样例:
create table Gastos_ConciliacionExcluida(IdRegistro int identity(1,1), MascaraTexto nvarchar(50), Activa bit default 1, Primary key (IDRegistro))
insert into Gastos_ConciliacionExcluida(MascaraTexto) Values ('%Reembolso%')
alter procedure SP_Gastos_ConciliacionExcluidaProcesar
as
declare cur cursor for select MascaraTexto From Gastos_ConciliacionExcluida where Activa=1
declare @Txt nvarchar(50)
open cur
fetch next from cur into @Txt
while @@Fetch_Status = 0
begin
update Gastos_BancoRegistro set PresumibleNoConciliable = 1
where Concepto like @txt
fetch next from cur into @Txt
end
close cur
deallocate cur
PostgreSQL有ANY或ALL表单:
WHERE col LIKE ANY( subselect )
or
WHERE col LIKE ALL( subselect )
其中子选择只返回一列数据。
