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

-- =============================================
-- 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'[吖-座]')
/*
阿明
*/