sql字符串分割

字符串：

declare @str varchar(1000)='0208588A_0208588,0098256A_0208588,0224208A_0224208,'

           -- 若字符串非常长，可用 varchar(max)

分割结果：

           

示例sql:

declare @str varchar(1000)='0208588A_0208588,0098256A_0208588,0224208A_0224208,'
--declare @str varchar(1000)='0080203A_0080203,0080215A_0080203,'

declare @sSplitChar varchar(1)=','
declare @tblExternal_id table(external_id varchar(100))
 
declare  @tmp   as   varchar(100)   
set   @str=rtrim(ltrim(@str)) + @sSplitChar     
set   @str=replace(@str,@sSplitChar+@sSplitChar, @sSplitChar) -- make sure @str ends with a @sSplitChar 

while(@str<>'')   
begin   
  set   @tmp=left(@str,charindex(@sSplitChar,@str,1)-1)   
  insert   @tblExternal_id   values(@tmp)   
  set   @str=stuff(@str,1,charindex(@sSplitChar,@str,1),'')   
end   
  
 --select * from @tblExternal_id
 select external_id, row_number() over(order by external_id) as RownNum from @tblExternal_id

参考来源：

​​http://www.360doc.com/content/12/0213/14/19692_186289261.shtml​​