sqlserver怎么找到拆分数据（SqlServer实现通过正则表达式拆分数据）
sqlserver怎么找到拆分数据（SqlServer实现通过正则表达式拆分数据）3.1 封装 Split 分隔函数（只需要执行一次即可重复调用）（Tip：由于爱好字段内容包含不规则字符，需要对各类不规则字符替换成统一字符后，才方便通过split函数进行分隔）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
需求说明
由于小伙伴们各自提交的爱好字符分隔各有千秋，希望能将大家的爱好进行详细分类，并汇总各类爱好的人数分别有多少。
目标拆解
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}\n\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}\n\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}\n\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
分类汇总结果
