Copy codeThe Code is as follows:
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 'China'
Union all select n 'China'
Union all select n 'people of China'
Union all select n 'Japan'
Union all select n 'Japanese'
Union all select n 'someone in my heart'
Union all select n 'people net'
Union all select n 'China is a great country'
Union all select n' we are ALL Chinese, ALL Chinese descendants, ALL dragon descendant'
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 'China ')
DECLARE @ searchSTR NVARCHAR (20)
SET @ searchSTR = N 'China'
Select id, [VALUE] FROM tb
Inner join fn_SplitStringToROWS (@ searchSTR) B
On charindex (B. v, a. VALUE)> 0
Where value like n' % [Chinese] %'
Group by id, VALUE
Order by count (DISTINCT v) DESC
Drop table tb
/*
V
----
Medium
Country
Person
(3 data columns are affected)
ID VALUE
----------- Begin ---------------------------------------------------------------------------------------------------------------
2 Chinese
3. Chinese people
9 we are all Chinese, both descendants of the Chinese people and descendants of the Chinese people.
6. Some girls in my heart
1 China
8. China is a great country
5. Japanese
7 People's Network
(8 Data columns are affected)
*/