需求说明

由于小伙伴们各自提交的爱好字符分隔各有千秋，希望能将大家的爱好进行详细分类，并汇总各类爱好的人数分别有多少。

目标拆解

1、创建临时表和数据

CREATE TABLE #tmp (
	ID INT IDENTITY(1, 1),
	Name NVARCHAR(50),
	Hobby NVARCHAR(100)
)

INSERT INTO #tmp ( Name, Hobby )
VALUES ('小亮', '打篮球')
INSERT INTO #tmp ( Name, Hobby )
VALUES ('小清', '画画、唱歌')
INSERT INTO #tmp ( Name, Hobby )
VALUES ('小舞', '画画 跳舞 书法')
INSERT INTO #tmp ( Name, Hobby )
VALUES ('小华', '跑步/踢足球、唱歌')

2、查看数据结构

原始数据

3、对数据进行拆分处理，生成明细表

（Tip：由于爱好字段内容包含不规则字符，需要对各类不规则字符替换成统一字符后，才方便通过Split函数进行分隔）

3.1 封装 Split 分隔函数（只需要执行一次即可重复调用）

CREATE FUNCTION [dbo].[SplitString]
(
    @Input NVARCHAR(MAX), --需要split的字符串
    @Separator NVARCHAR(MAX) = ',' --格式化时分隔符，默认分隔符为,
)
RETURNS @TABLE TABLE 
(
    [Id] INT IDENTITY(1, 1),
    [Value] NVARCHAR(MAX)
) 
AS
BEGIN
    DECLARE @Index INT, @Entry NVARCHAR(MAX)
    SET @Index = CHARINDEX(@Separator, @Input)

    WHILE (@Index > 0)
    BEGIN
        SET @Entry = LTRIM(RTRIM(SUBSTRING(@Input, 1, @Index-1)))
        
        IF (@Entry<>'')
        BEGIN
            INSERT INTO @TABLE([Value]) VALUES(@Entry)
        END

        SET @Input = SUBSTRING(@Input, @Index + DATALENGTH(@Separator)/2, LEN(@Input))
        SET @Index = CHARINDEX(@Separator, @Input)
    END
    
    SET @Entry = LTRIM(RTRIM(@Input))
    IF (@Entry<>'')
    BEGIN
		INSERT INTO @TABLE([Value]) VALUES(@Entry)
    END

    RETURN
END

3.2 封装 正则替换 函数（只需要执行一次即可重复调用）

CREATE FUNCTION [dbo].[RegexReplace] (  
    @string VARCHAR(MAX),   --被替换的字符串  
    @pattern VARCHAR(255),  --替换模板  
    @replacestr VARCHAR(255),   --替换后的字符串  
    @IgnoreCase INT = 0 --0区分大小写 1不区分大小写  
)
RETURNS VARCHAR(8000)  
AS   
BEGIN  
    DECLARE @objRegex INT, @retstr VARCHAR(8000)  
    --创建对象  
    EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT  
    --设置属性  
    EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern  
    EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase  
    EXEC sp_OASetProperty @objRegex, 'Global', 1  
    --执行  
    EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr  
    --释放  
    EXECUTE sp_OADestroy @objRegex  
    RETURN @retstr  
END  
GO

--保证正常运行的话，需要将Ole Automation Procedures选项置为1    
EXEC sp_configure 'show advanced options', 1    
RECONFIGURE WITH OVERRIDE   
EXEC sp_configure 'Ole Automation Procedures', 1    
RECONFIGURE WITH OVERRIDE

3.3 通过执行 正则替换函数 查看字符替换效果（统一替换成 | 字符）

DECLARE @RegexStr NVARCHAR(100);
SET @RegexStr = '[\s\p{P}
\r=+$￥<>^`~|，,/\ ;、]';  -- 可能遇到的分隔符都可以放入
SELECT [dbo].[RegexReplace](ISNULL(Hobby, 'NULL'), @RegexStr, '|', 1) FROM #tmp

正则替换后

3.4 通过执行 Split函数 查看字符分隔成行数据效果

DECLARE @RegexStr NVARCHAR(100), @Hobby NVARCHAR(100);
SET @RegexStr = '[\s\p{P}
\r=+$￥<>^`~|，,/\ ;、]';
SELECT @Hobby = [dbo].[RegexReplace](ISNULL(Hobby, 'NULL'), @RegexStr, '|', 1) FROM #tmp

SELECT t.Value
FROM [dbo].[SplitString](@Hobby, '|') AS t
JOIN #tmp o ON t.ID = o.ID

分隔后

3.5 创建明细表，对爱好数据进行遍历，通过正则替换拆分后写入

CREATE TABLE #detail (
	ID INT IDENTITY(1, 1),
	tID INT,  --原始临时表主键ID
	Name NVARCHAR(50),
	Hobby NVARCHAR(100)
)

SELECT * INTO #t FROM #tmp

WHILE EXISTS (SELECT 1 FROM #t)
BEGIN
    DECLARE @ID INT, @Name NVARCHAR(50), @Hobby NVARCHAR(100);
	DECLARE @RegexStr NVARCHAR(100);
	
	SET @RegexStr = '[\s\p{P}
\r=+$￥<>^`~|，,/\ ;、]';

	SELECT TOP 1 @ID = ID, @Name = Name, @Hobby = [dbo].[RegexReplace](ISNULL(Hobby, 'NULL'), @RegexStr, '|', 1) FROM #t ORDER BY ID;

	-- 判断是否多个爱好
	IF (PATINDEX('%|%', @Hobby) > 0)
	BEGIN
	    INSERT INTO #detail ( tID, Name, Hobby )
		SELECT @ID, @Name, Value FROM [dbo].[SplitString](@Hobby, '|')
	END
	ELSE
    BEGIN
        INSERT INTO #detail ( tID, Name, Hobby )
        VALUES (@ID, @Name, @Hobby)
    END

	DELETE FROM #t WHERE ID = @ID;
END
DROP TABLE #t

SELECT * FROM #detail

拆分后明细

4、对明细数据进行汇总，汇总各类爱好的人数

SELECT Hobby, COUNT(*) AS ahCount
FROM #detail
GROUP BY Hobby
ORDER BY COUNT(*) DESC

分类汇总结果