sql字符串分割split函数

sql字符串分割split函数

使用指定的字符分割字符串。

思路:1,先计算出指定的分割字符在字符串中的索引,

2.然后再分割字符串。

sql代码如下:

declare@strordernvarchar(200)declare@ismulorderintdeclare@firstOrdernvarchar(200)declare@secondOrdernvarchar(200)

set@strorder=’LastModifyDateDesc,OurPriceAsc’

selectCHARINDEX(‘,’,@strorder,0)–selectLEN(@strorder)–selectSUBSTRING(@strorder,0,CHARINDEX(‘,’,@strorder,0))–selectSUBSTRING(@strorder,CHARINDEX(‘,’,@strorder,0)+1,LEN(@strorder))set@ismulorder=CHARINDEX(‘,’,@strorder,0)if@ismulorder0beginset@firstOrder=’p.’+SUBSTRING(@strorder,0,CHARINDEX(‘,’,@strorder,0))set@secondOrder=’p.’+SUBSTRING(@strorder,CHARINDEX(‘,’,@strorder,0)+1,LEN(@strorder))set@strorder=@firstOrder+’,’+@secondOrderend

select@strorder

利用split函数

create function [dbo].[split]澳门新葡亰手机版,(@SourceSql varchar(8000),@StrSeprate
varchar(10))returns @temp table(F1 varchar(100))asbegin declare @i int
set @SourceSql = rtrim(ltrim(@SourceSql)) set @i =
charindex(@StrSeprate,@SourceSql) while @i = 1 begin if
len(left(@SourceSql,@i-1))0 begin insert @temp
values(left(@SourceSql,@i-1)) end set
@SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set
@i=charindex(@StrSeprate,@SourceSql) end if @SourceSql ” insert @temp
values(@SourceSql) returnendselect * from
split(‘,,777,,11,,888,88,,1122,888,88,77,,00,,00′,’,’)