请教个提取字段中数据的问题

[Jofan]

数据中有一列是这样的
___________________
姓名*职位*电话*邮箱
___________________

如：
________________________________
张三*教师*1234567*[e-mail]
________________________________

我现在要用select把这一列的数据提取出来变成4列
请问这个语句怎么写，请会的朋友赐教！

[-狙击手-]

declare @s varchar(8000)
set @s = '姓名*职位*电话*邮箱'

select @s = replace(@s,'*',''',''')

exec('select '''+@s+'''')

[-狙击手-]

/*
---- ---- ---- ----
姓名 职位 电话 邮箱

*/

[dawugui]

select 
PARSENAME(replace(col,'*','.'),4),
PARSENAME(replace(col,'*','.'),3),
PARSENAME(replace(col,'*','.'),2),
PARSENAME(replace(col,'*','.'),1)
from tb

[-狙击手-]

declare @s varchar(8000)
set @s = '张三*教师*1234567*[e-mail] '

select @s = replace(@s,'*',''',''')

exec('select '''+@s+'''')
/*
                                 
---- ---- ------- -------------- 
张三   教师   1234567 [e-mail] 
*/

[dawugui]

create table tb(col varchar(50))
insert into tb values('姓名*职位*电话*邮箱')
insert into tb values('张三*教师*1234567*[e-mail]')
go
select 
PARSENAME(replace(col,'*','.'),4),
PARSENAME(replace(col,'*','.'),3),
PARSENAME(replace(col,'*','.'),2),
PARSENAME(replace(col,'*','.'),1)
from tb

drop table tb

/*
                                                                                                                                                                                                                                                                                                                                                                                                   
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
姓名                                                                                                                               职位                                                                                                                               电话                                                                                                                               邮箱
NULL                                                                                                                             NULL                                                                                                                             NULL                                                                                                                             NULL

(2 行受影响)
*/

[dobear_0922]

create table tb(col varchar(64))
insert tb select '张三*教师*1234567*[e-mail]'


select parsename(replace(replace(col,'.','#'),'*','.'),4)
	,parsename(replace(replace(col,'.','#'),'*','.'),3)
	,parsename(replace(replace(col,'.','#'),'*','.'),2)
	,replace(parsename(replace(replace(col,'.','#'),'*','.'),1),'#','.')
from tb

/*
-------- ------------- ------------- ----------------------------
张三	     教师	        1234567	     [e-mail]                
(1 row(s) affected)
*/

drop table tb

[dawugui]

create table tb(col varchar(50))
insert into tb values('姓名*职位*电话*邮箱')
insert into tb values('张三*教师*1234567*[e-mail]')
go
select 
col1 = replace(PARSENAME(replace(replace(col,'.','$'),'*','.'),4),'$','.'),
col2 = replace(PARSENAME(replace(replace(col,'.','$'),'*','.'),3),'$','.'),
col3 = replace(PARSENAME(replace(replace(col,'.','$'),'*','.'),2),'$','.'),
col4 = replace(PARSENAME(replace(replace(col,'.','$'),'*','.'),1),'$','.')
from tb

drop table tb

/*
col1                                                                                                                                                                                                                                                             col2                                                                                                                                                                                                                                                             col3                                                                                                                                                                                                                                                             col4
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
姓名                                                                                                                                                                                                                                                               职位                                                                                                                                                                                                                                                               电话                                                                                                                                                                                                                                                               邮箱
张三                                                                                                                                                                                                                                                               教师                                                                                                                                                                                                                                                               1234567                                                                                                                                                                                                                                                          [e-mail]

(2 行受影响)

*/

[dawugui]

[code=SQL]create table tb(col varchar(50))
insert into tb values('姓名*职位*电话*邮箱')
insert into tb values('张三*教师*1234567*[e-mail]')
go
select 
col1 = replace(PARSENAME(replace(replace(col,'.','$'),'*','.'),4),'$','.'),
col2 = replace(PARSENAME(replace(replace(col,'.','$'),'*','.'),3),'$','.'),
col3 = replace(PARSENAME(replace(replace(col,'.','$'),'*','.'),2),'$','.'),
col4 = replace(PARSENAME(replace(replace(col,'.','$'),'*','.'),1),'$','.')
from tb

drop table tb

/*
col1    col2    col3    col4
-------------------------------------
姓名    职位    电话    邮箱
张三    教师    1234567 [e-mail]

(2 行受影响)

*/

[/code]

[pt1314917]

create table kk(name varchar(100))
insert into kk select '张三*教师*1234567*[e-mail]'
insert into kk select '小毛*学生*8888888*[e-mail]'


alter function kkkk(@name varchar(100))
returns varchar(500)
as
begin
	declare @sql varchar(500)
	set @sql=replace(@name+'*','*',''' columns,''')
	return ''''+left(@sql,len(@sql)-2)
end


declare @sql varchar(8000)
select @sql=isnull(@sql+' union select ','select ')+dbo.kkkk(name) from kk
exec(@sql) 

[Jofan]

太快了，已经解决。谢谢上面几位：）