IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE tb (ID INT IDENTITY(1,1),VALUE NVARCHAR(100))
INSERT tb SELECT N'中國(guó)'
UNION ALL SELECT N'中國(guó)人'
UNION ALL SELECT N'中國(guó)人民'
UNION ALL SELECT N'日本'
UNION ALL SELECT N'日本人'
UNION ALL SELECT N'我的心中有人姑娘'
UNION ALL SELECT N'人民網(wǎng)'
UNION ALL SELECT N'中國(guó)是個(gè)偉大的國(guó)家'
UNION ALL SELECT N'我們都是中國(guó)人，都是炎黃子孫，都是龍人傳人'
IF OBJECT_ID('fn_SplitStringToROWS')IS NOT NULL DROP FUNCTION fn_SplitStringToROWS
GO
CREATE FUNCTION fn_SplitStringToROWS
(
@str NVARCHAR(100)
)
RETURNS @t TABLE(v NVARCHAR(2))
AS
BEGIN
DECLARE @i INT
SET @i=1
WHILE @i<=LEN(@str)
BEGIN
INSERT @t SELECT SUBSTRING(@str,@i,1)
SET @i=@i+1
END
RETURN
END
GO
SELECT * FROM DBO.fn_SplitStringToROWS(N'中國(guó)人')
DECLARE @searchSTR NVARCHAR(20)
SET @searchSTR=N'中國(guó)人'
SELECT ID,[VALUE] FROM tb a
INNER JOIN fn_SplitStringToROWS(@searchSTR) b
ON CHARINDEX(b.v,a.VALUE)>0
WHERE VALUE LIKE N'%[中國(guó)人]%'
GROUP BY ID,VALUE
ORDER BY COUNT(DISTINCT v) DESC
DROP TABLE tb