用TSQL分割字符串

 

经常会有需要截取一个在表字段中某个部分。分享函数SplitList  来完成需要. 

先看个例子

实例：

要求截取最后那段带有.mp3的字段

declare    @rowData  nvarchar   (  300 )

 

set  @rowData  =   '\sql\2014\01\11\49_1112323.mp3'

 

SELECT  Data  FROM   dbo  . SplitList   (  @rowData ,   '\'  )   where  Data  like   '%.MP3'

 

结果

Data
----------------------------------------------------------------------------------------------------
49_1112323.mp3

(1 row(s) affected)

 

ALTER FUNCTION [dbo].[SplitList]
(
@RowData nvarchar( Max)='' ,
@SplitOn nvarchar( 5)=''
)
RETURNS @RtnValue table
(
Id int identity(1 ,1),
Data nvarchar( 100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex (@SplitOn, @RowData)>0 )
Begin
Insert Into @RtnValue ( data)
Select
Data = ltrim(rtrim (Substring( @RowData,1 ,Charindex( @SplitOn,@RowData )-1)))

Set @RowData = Substring(@RowData ,Charindex( @SplitOn,@RowData )+1, len(@RowData ))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue ( data)
Select Data = ltrim( rtrim(@RowData ))

Return
END

 

转载于:https://www.cnblogs.com/rwthyu/p/3521780.html