SQL Server字符串切割函数

复制代码 代码如下:

CREATE FUNCTION fGetStrBySplit

(

@Source VARCHAR(max),

@Index INT,

@SplitChar VARCHAR(1)

)

RETURNS varchar(MAX)

AS

BEGIN

DECLARE @Len INT

DECLARE @n INT = 0

DECLARE @ChIndex INT

DECLARE @Result VARCHAR(MAX)

--获取总长度

SET @Len = LEN(@Source)

--获取切割字符位置

SET @ChIndex = CHARINDEX(@SplitChar,@Source)

WHILE @ChIndex > 0

BEGIN

IF(@n = @Index)

BEGIN

SET @Source = SUBSTRING(@Source,0,@ChIndex)

BREAK

END

SET @Source = SUBSTRING(@Source,@ChIndex+1,@Len)

SET @ChIndex = CHARINDEX(@SplitChar,@Source)

SET @Len = LEN(@Source)

SET @n = @n + 1

END

RETURN @Source

END

GO

--调用

DECLARE @value VARCHAR(max)

SET @value = dbo.fGetStrBySplit('645002*01_45854_183677_12',0,'_')

PRINT @value

结果：

645002*01

--1

45854

--2

183677