date1="2010-1-15 14:07:45" pe="pp" id="9411" name="001"
date1="2010-1-15 14:07:06" pe="pp" id="09411" name="002"
date1="2010-1-15 14:07:09" pe="pp" id="109411" name="003"
请问如何截取为
id name date1
9411 001 2010-1-15 14:07:45
09411 002 2010-1-15 14:07:06
109411 003 2010-1-15 14:07:09
date1="2010-1-15 14:07:06" pe="pp" id="09411" name="002"
date1="2010-1-15 14:07:09" pe="pp" id="109411" name="003"
请问如何截取为
id name date1
9411 001 2010-1-15 14:07:45
09411 002 2010-1-15 14:07:06
109411 003 2010-1-15 14:07:09
或者excel也可以直接读取
这个id,name 不好截取?
我想应该是从id="开始截取,碰到"结束
从name="开始截取,碰到第一个"结束?
敢问高手如何截取?
--先导入DB再处理,直接导入一个栏位中即可
create table T(col nvarchar(300))insert into T values('date1="2010-1-15 14:07:45" pe="pp" id="9411" name="001"')
insert into T values('date1="2010-1-15 14:07:06" pe="pp" id="09411" name="002" ')
insert into T values('date1="2010-1-15 14:07:09" pe="pp" id="109411" name="003" ')
GOselect parsename(col,1) as [name], parsename(col,2) as [id], parsename(col,3) as [pe], parsename(col,4) as [date]
from
(
select replace(replace(replace( replace(replace(col,'date1="',''),'" pe="','.'),'" id="','.'),'" name="','.') ,'"','') as col
from T
) A/*
name id pe date
-------------------------------------------------
001 9411 pp 2010-1-15 14:07:45
002 09411 pp 2010-1-15 14:07:06
003 109411 pp 2010-1-15 14:07:09*/
GoDrop table T
declare @y xml
set @x='date1="2010-1-15 14:07:45" pe="pp" id="9411" name="001" date1="2010-1-15 14:07:06" pe="pp" id="09411" name="002" date1="2010-1-15 14:07:09" pe="pp" id="109411" name="003" '
set @x=replace(@x,'date1','/><x date1')
set @x=right(@x,len(@x)-1)+'/>'
set @y=cast(@x as xml)select T.col.value('@date1','datetime') date1,
T.col.value('@pe','varchar(20)') pe,
T.col.value('@id','varchar(20)') id,
T.col.value('@name','varchar(20)') name
from @y.nodes('//x') as T(col)--result
/*date1 pe id name
----------------------- -------------------- -------------------- --------------------
2010-01-15 14:07:45.000 pp 9411 001
2010-01-15 14:07:06.000 pp 09411 002
2010-01-15 14:07:09.000 pp 109411 003(3 行受影响)*/