函数代码:
USE 数据库名称
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[f_splitSTR](
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen = LEN(@split + 'a') - 2
WHILE CHARINDEX(@split,@s) > 0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s) - 1))
SET @s = STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
使用方式:
select cast(col as varchar) as 名称
from f_splitSTR(rtrim('小米,小红'),',')
结果: