SQL Server字符串切割

 1 CREATE FUNCTION fGetStrBySplit 

 2 (

 3     @Source VARCHAR(max),

 4     @Index INT,

 5     @SplitChar VARCHAR(1)

 6 )

 7 RETURNS varchar(MAX)

 8 AS

 9 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