SQL截取表中字段字符问题 求一储存过程

[ghost_1437]

求一储存过程
A表 字段
123_12_124
1234_125_1243

要求生成以下结果:
字段 字段1 字段2 字段3
123_12_124 123 12 124
1234_125_1243 1234 125 1243

[ghost_1437]

修:
求一储存过程
A表 字段
123_12_124
1234_125_1243

要求以"_"为分隔符,分三段生成以下结果:
字段 字段1 字段2 字段3
123_12_124 123 12 124
1234_125_1243 1234 125 1243

[samfeng_2003]

DECLARE @T TABLE
(
 COL VARCHAR(80)
)

INSERT INTO @T
SELECT '123_12_124' UNION ALL   
SELECT '1234_125_1243'



SELECT COL,
       COL1 = PARSENAME(REPLACE(COL,'_','.'),3),
       COL2 = PARSENAME(REPLACE(COL,'_','.'),2),
       COL3 = PARSENAME(REPLACE(COL,'_','.'),1)
 FROM @T

COL        COL1       COL2       COL3       
---------- ---------- ---------- ---------- 
123_12_124 123        12         124
1234_125_1 1234       125        1243

（所影响的行数为 2 行）

[bootupnow]

create table #tmpa(
	c varchar(50)
)

create table #tmpb(
	c varchar(60),
	c1 varchar(20),
	c2 varchar(20),
	c3 varchar(20)
)

insert into #tmpa 
select '123_12_124' union all
select '1234_125_1243'  


declare @s varchar(200),@tmpStr varchar(200),@hb varchar(200),
		@pos int,@sql varchar(8000)
select  @pos = 0,@s = '',@hb = ''
declare cur cursor for
select c from #tmpa
open cur
fetch next from cur into @s
while (@@fetch_status = 0)
begin
	set @tmpStr = @s
	set @pos = charindex('_',@s,1)
	while (@pos != 0)
	begin 
		set @hb =  @hb + ''',''' + substring(@tmpStr,1,@pos - 1)
		set @tmpStr = substring(@tmpStr,@pos + 1,len(@tmpStr))
		set @pos = charindex('_',@tmpStr,1)
		if(len(@tmpStr) != 0 and @pos = 0)
			 set @hb =  @hb + ''',''' + substring(@tmpStr,1,len(@tmpStr)) + ''''
	end
	set @sql  = 'select ''' + @s + ''',' + right(@hb,len(@hb) - 2)
	exec('insert into #tmpb ' + @sql)
	select @s = '',@tmpStr = '',@hb = ''
	fetch next from cur into @s
end
close cur
deallocate cur
 
select * from tmpb

自己汗自己一个，晕。。。
c c1 c2 c3
123_12_124 123 12 124
1234_125_1243 1234 125 1243

[bootupnow]

拜下 samfeng_2003 ，巧！^_^，PARSENAME 刚刚好

[sp4]

PARSENAME用得巧，哈哈

[bsh_ly]

  
SELECT
     COL,
     left(COL,charindex('_',COL)) AS col_1,
     substring(COL,
               charindex('_',COL)+1,
               charindex('_',COL,charindex('_',COL)+1)
              ) AS col_2,
     rigth(COL,charindex('_',COL,charindex('_',COL)+1)+1) AS col_3
FROM TABLE_A

[bsh_ly]

  
SELECT
     COL,
     left(COL,charindex('_',COL)) AS col_1,
     substring(COL,
               charindex('_',COL)+1,
               charindex('_',COL,charindex('_',COL)+1)
              ) AS col_2,
     rigth(COL,charindex('_',COL,charindex('_',COL)+1)+1) AS col_3
FROM TABLE_A

[bsh_ly]

SELECT
     COL,
     left(COL,charindex('_',COL)) AS col_1,
     substring(COL,
               charindex('_',COL)+1,
               charindex('_',COL,charindex('_',COL)+1)
              ) AS col_2,
     rigth(COL,charindex('_',COL,charindex('_',COL)+1)+1) AS col_3
FROM TABLE_A

[hamburger]

PARSENAME巧是巧 但是只能截取4個 超過4個的話 就不好用了

[liangCK]

DECLARE @T TABLE
(
 COL VARCHAR(80)
)

INSERT INTO @T
SELECT '123_152_5124' UNION ALL   
SELECT '1234_125_1243'


SELECT COL,
       col1=LEFT(COL,CHARINDEX('_',COL)-1),
       COL2=SUBSTRING(COL,CHARINDEX('_',COL)+1,CHARINDEX('_',STUFF(COL,1,CHARINDEX('_',COL),''))-1),
       COL3=RIGHT(COL,CHARINDEX('_',REVERSE(COL))-1)
 FROM @T

[zhoulei_2005]

拜下 samfeng_2003 ，巧！^_^，PARSENAME 刚刚好