Office中国论坛/Access中国论坛
标题:
【函数】：提取汉字、英文字母和数值
[打印本页]
作者:
roych
时间:
2023-5-26 20:03
标题:
【函数】：提取汉字、英文字母和数值
前不久在某论坛发现有人问怎么提取汉字、英文字母和数值。但SQL Server是不支持正则表达式的，因此需要写自定义函数。没错，我是让ChatGPT写的，这货写了三四次才写正确，真是的。
现在分享给大家，希望能在工作中用上。
create FUNCTION ExtractFromInputString
(
@inputString NVARCHAR(MAX),
@extractType NVARCHAR(50)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @outputString NVARCHAR(MAX) = ''
IF @extractType = 'alpha'
BEGIN
SET @outputString = (
SELECT SUBSTRING(@inputString, n, 1)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.columns) AS Numbers
WHERE SUBSTRING(@inputString, n, 1) COLLATE Latin1_General_BIN LIKE '[A-Za-z]'
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
)
END
ELSE IF @extractType = 'numeric'
BEGIN
SET @outputString = (
SELECT SUBSTRING(@inputString, n, 1)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.columns) AS Numbers
WHERE SUBSTRING(@inputString, n, 1) LIKE '[0-9]'
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
)
END
ELSE IF @extractType = 'chinese'
BEGIN
SET @outputString = (
SELECT SUBSTRING(@inputString, n, 1)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.columns) AS Numbers
WHERE UNICODE(SUBSTRING(@inputString, n, 1)) >= 19968
AND UNICODE(SUBSTRING(@inputString, n, 1)) <= 40959
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'
)
END
RETURN @outputString
END
复制代码
调用方法：
select *, dbo.ExtractFromInputString(子类目, 'chinese') as 汉字,
dbo.ExtractFromInputString(子类目, 'numeric') as 数字,
dbo.ExtractFromInputString(子类目, 'alpha') as 字母 from testLag
复制代码
附件如下所示：
[attach]64565[/attach]
作者:
worryd1
时间:
2023-11-13 21:01
谢谢分享，先收藏
欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)
Powered by Discuz! X3.3
现在分享给大家，希望能在工作中用上。