sql server 将一个以特定字符分开的参数转换成多行

FID                  url
-------------------- ---------------------
100                  [04],[1402],[9999],[0106]

在实际应用是经常会碰到这种参数，在sql中直接调用不了必须进行处理才能使用在in或者别的场景中

with base_para as  
 (
 select URL=cast(left(URL,charindex(',',URL+',')-1) as nvarchar(100)),Split=CAST( right(URL+',',len(URL) - charindex(',',URL+',') + 1) as nvarchar(100))
	from T_HIS_SYSPARAMS WHERE FNAME = '体检统计的方式'
	 
 union all 
 select URL=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= CAST( right(Split,len(Split) - charindex(',',Split))
	as nvarchar(100)) from base_para where split <> '' 
 ) 
 select URL , Split from base_para 

URL    Split
---------------
[04] ,  [1402],[9999],[0106],
[1402], [9999],[0106],
[9999], [0106],
[0106]