日期:2014-05-17 浏览次数:20497 次
IF object_id('tempdb..#temp') IS NOT NULL DROP TABLE #temp
go
CREATE TABLE #temp(id INT IDENTITY PRIMARY KEY, [name] NVARCHAR(MAX))
INSERT #temp([name])
SELECT '我的' UNION ALL
SELECT '匹配度' UNION ALL
SELECT '我配度大' UNION ALL
SELECT '我1配2大3'
go
DECLARE @str NVARCHAR(100)
SET @str = N'我的匹配度最大'
SELECT m.id,n.NAME, m.匹配字符数
FROM
(
SELECT id, 匹配字符数 = SUM(是否匹配) FROM
(
SELECT
a.id,
name=SUBSTRING(a.[name], b.number, 1),
是否匹配=(CASE WHEN CHARINDEX(SUBSTRING(a.[name], b.number, 1), @str) > 0 THEN 1 ELSE 0 END)
FROM #temp a
INNER JOIN master..spt_values b
ON b.type = 'p' AND b.number BETWEEN 1 AND LEN(a.[name])
) t
GROUP BY id
) m
INNER JOIN #temp n
ON m.id = n.id
/*
id NAME 匹配字符数
1 我的 2
2 匹配度 3
3 我配度大 4
4 我1配2大3 3
*/