将字符串分解为单个字符的表

-- =============================================
-- Author:		yenange
-- Create date: 2022-10-14
-- Description:	将字符串分解为字符
-- =============================================
CREATE or alter FUNCTION dbo.Fun_SplitStringToCharTable
(	
	@s nvarchar(max)
)
RETURNS TABLE 
AS
RETURN 
(
	with cteLen as(
		select len(@s) as leng
	)
	,cte as (
		select top (select leng from cteLen) ROW_NUMBER() over(order by (select 1)) as rid from master.dbo.spt_values as s with(nolock)
			cross apply master.dbo.spt_values as s2 with(nolock)
	)
	select rid,substring(@s,rid,1) as c from cte as a where rid<=len(@s)
)
GO
--找出所有的数字
select * from dbo.Fun_SplitStringToCharTable('abcd1234defg') as f where f.c like '[0-9]' order by rid
--找出所有的英文
select * from dbo.Fun_SplitStringToCharTable('abcd1234defgZ') as f where f.c like '[a-z]' order by rid
--找出所有的数字并合成为一个字符串
select (select ''+f.c  from dbo.Fun_SplitStringToCharTable('abcd1234defg') as f where f.c like '[0-9]' order by rid for xml path('')) as string



再进一步,做一个更好用的标量函数

-- =============================================
-- Author:		yenange
-- Create date: 2022-10-15
-- Description:	获取字符串中能匹配到的部分
-- =============================================
CREATE or alter FUNCTION dbo.Fun_GetMatchString
(
	@str nvarchar(max),
	@like varchar(50)
)
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @r nvarchar(max)
	set @r=(select ''+f.c  from dbo.Fun_SplitStringToCharTable(@str) as f where f.c like @like order by rid for xml path(''))
	RETURN @r
END
GO
--筛选出数字和"
select dbo.Fun_GetMatchString('13812345678"abc','[0-9"]')
/*
13812345678"
*/
--筛选出汉字
select dbo.Fun_GetMatchString('13812345678"abc阿明a',N'[吖-座]')
/*
阿明
*/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值