sql 字段值拆分 更新

1：  新建分割函数

 

CREATE Function [dbo].[GetSplitTxt]
(
@String nvarchar(max), --要分割的字符串
@Split nvarchar(10), --分隔符号
@Num int --取第几个，最左边默认为第1个
)
returns nvarchar(4000)
as
begin
declare @location int
declare @length int
declare @StartOld int
declare @StartNew int
declare @Txt nvarchar(4000)

set @String=ltrim(rtrim(@String))
set @location=charindex(@split,@String)
set @StartOld = 0
set @StartNew = 0
set @length = 1
set @Txt = ''
if @location = 0 and @Num = 1
begin
set @Txt = @String
end
while @location <> 0
begin
set @StartOld = @StartNew
if @length = @Num
begin
if @Num = 1
begin
set @Txt = substring(@String,1,@location-1)
end
else
begin
set @Txt = substring(@String,@StartNew+Len(@Split)-1,@location-@StartNew+1-Len(@Split))
end
end
set @StartNew = @location+1
set @location = charindex(@split,@String,@StartNew)
set @length = @length+1
if @location = 0 and @length = @Num
begin
set @Txt = substring(@String,@StartNew+Len(@Split)-1,999999999)
end
end
return @Txt
end

 

2：调用函数

UPDATE [GomeCPS].[dbo].[TcandOrder] set sid =[GomeCPS].[dbo].[GetSplitTxt] (
TrackingCode
,'_'
,2) WHERE TrackingCode LIKE 'cps_%'

 

 分割的字符串 ：  cps_64_64_25684380477921