在SQL里怎么拆分一个字段的值，并取出值？

--如果位置固定select substring(name,5,3) from tb--如果位置不固定select substring(name , charindex('-',name)+1 , charindex('-',name,charindex('-',name)+1) - charindex('-',name) -1) from tb
------解决方案--------------------
SQL code
位置不固定declare @str1 nvarchar(100),@str2 nvarchar(100)set @str1 = 'ddddddqq_bbdddqq_cccc_eee' set @str2=stuff(@str1,charindex('_',@str1,1),1,'m')select substring(@str1,charindex('_',@str1,1)+1,charindex('_',@str2,1)-charindex('_',@str1,1)-1)
------解决方案--------------------
create table b (name varchar(50))
insert b select 'dfsdfds_dddfa_fsdfa_dsfasd'  union all select  
'sdfsdaf_fdsfg_fds_fsdafs' union all select  
'dafasdf_fasdf_fdsfsd' union all select  
'sdfds_dsfasd'  
select substring(name , charindex( '_',name+'_')+1 , charindex( '_',name+'_',charindex( '_',name+'_')+1) - charindex( '_',name+'_') -1) from b  

------解决方案--------------------
SQL code
create function kkk(@text varchar(100))returns varchar(50)asbegin    declare @p varchar(50)    declare @num int    if(charindex('_',@text)>0)    begin        set @text=substring(@text,charindex('_',@text)+1,len(@text))        set @num=charindex('_',@text)        if(@num>0)set @p=substring(@text,1,charindex('_',@text)-1)        else set @[email protected]    end    return @pend调用select dbo.kkk('_bbb_asdf')
------解决方案--------------------
SQL code
declare @num intdeclare @str1 nvarchar(100),@str2 nvarchar(100)set @str1 = '_bbdddqqeee_' set @str2=stuff(@str1,charindex('_',@str1,1),1,'m')if(charindex('_',@str2,1)>charindex('_',@str1,1))   set @num=charindex('_',@str2,1)-charindex('_',@str1,1)-1else   set @num=len(@str1)-charindex('_',@str1,1)select substring(@str1,charindex('_',@str1,1)+1,@num)/*----------------------------------------bbdddqqeee*/
------解决方案--------------------
可以在字符的后面先加上个'_',这样应该就可以了


declare @name as varchar(20)
set @name = 'qqq_bbb_cccc_eee' + '_'

select 位置固定 = substring(@name , 5 ,3)  

select 位置不固定 = substring(@name , charindex('_',@name)+1 , charindex('_',@name,charindex('_',@name)+1) - charindex('_',@name) -1)