我有一个类似以下的SQL请求:
SELECT *
FROM table
WHERE lower(title) LIKE lower('%It's a beautiful string i think%')
我需要检查我的字符串
It's a beautiful string i think
中是否至少有两个单词包含在我的字段标题中... 我该怎么做?例如,如果在我的字段标题中出现字符串
I think it's beautiful
,这个查询应该返回这个对象...谢谢!
你可以通过动态生成以下SQL语句:
SELECT title, count(*)
FROM
(
SELECT title
FROM table1
WHERE (' ' + lower(title) + ' ') LIKE lower('% It %')
UNION ALL
SELECT title
FROM table1
WHERE (' ' + lower(title) + ' ') LIKE lower('% s %')
UNION ALL
SELECT title
FROM table1
WHERE (' ' + lower(title) + ' ') LIKE lower('% a %')
UNION ALL
SELECT title
FROM table1
WHERE (' ' + lower(title) + ' ') LIKE lower('% beautiful %')
UNION ALL
SELECT title
FROM table1
WHERE (' ' + lower(title) + ' ') LIKE lower('% string %')
UNION ALL
SELECT title
FROM table1
WHERE (' ' + lower(title) + ' ') LIKE lower('% I %')
UNION ALL
SELECT title
FROM table1
WHERE (' ' + lower(title) + ' ') LIKE lower('% think %')
) AS table2
GROUP BY title
HAVING COUNT(*) >= 2
存储过程可能更有效率,而且您可以在服务器端完成整个工作。
CREATE FUNCTION [dbo].[CheckSentece] (@mainSentence varchar(128), @checkSentence varchar(128))
RETURNS NUMERIC AS
BEGIN
SET @mainSentence=LOWER(@mainSentence)
SET @checkSentence=LOWER(@checkSentence)
DECLARE @pos INT
DECLARE @word varchar(32)
DECLARE @count NUMERIC
SET @count=0
WHILE CHARINDEX(' ', @checkSentence) > 0
BEGIN
SELECT @pos = CHARINDEX(' ', @checkSentence)
SELECT @word = SUBSTRING(@checkSentence, 1, @pos-1)
DECLARE @LEN NUMERIC
//Simple containment check, better to use another charindex loop to check each word from @mainSentence
SET @LEN=(SELECT LEN(REPLACE(@mainSentence,@word,'')))
if (@LEN<LEN(@mainSentence)) SET @count=@count+1
SELECT @checkSentence = SUBSTRING(@checkSentence, @pos+1, LEN(@checkSentence)-@pos)
END
return @count
END
并获取第二个句子中包含在第一个句子中的单词数量
'i a'
的行也应该被返回,对吗? - Sergey KalinichenkoLIKE ('_% _% %')
。 - Ayush