|
前不久在某论坛发现有人问怎么提取汉字、英文字母和数值。但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
复制代码 附件如下所示:
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有帐号?注册
x
|