<top:28.324;left:20.409;"><nobr>
<span style="font-size:9.111;">Printed:</span>
<span style="font-size:9.111;">09-Jul-2014</span>
<span style="font-size:9.111;">19:59:17</span>
</nobr></div>
在SQL里，这样的一串字符要怎么截取成
Printed:09-Jul-2014 19:59:17
注意里面有换行的
------解决方案--------------------
这个参考下，不是最好的实现方式
declare @s varchar(max)
set @s= '<top:28.324;left:20.409;"><nobr>
<span style="font-size:9.111;">Printed:</span>
<span style="font-size:9.111;">09-Jul-2014</span>
<span style="font-size:9.111;">19:59:17</span>
</nobr></div>'
;with cte as
(
select row_number () over ( order by snum desc )as rn1,snum
from (
select distinct charindex ('">',@s,number) as snum from master.dbo.spt_values where type='p' and number >=1 and number <len (@s)
)t
)
,cte1 as
(
select row_number () over ( order by enum desc )as rn2,enum
from (
select distinct charindex ('</span>',@s,number) as enum from master.dbo.spt_values where type='p' and number >=1 and number <len (@s)
)t2
)
select substring (@s,cte.snum +2,cte1.enum-cte.snum-2) from cte
inner join cte1 on cte.rn1=cte1.rn2 where cte.snum<>0 and cte1.enum<>0
--(无列名)
--19:59:17
--09-Jul-2014
--Printed:
------解决方案--------------------
-- 先得把 <nobr> .. </nobr> 这一段提取出来，然后用 xml 进行处理
DECLARE @text varchar(max)
DECLARE @i1 int
DECLARE @i2 int
SET @text = '<top:28.324;left:20.409;"><nobr>
<span style="font-size:9.111;">Printed:</span>
<span style="font-size:9.111;">09-Jul-2014</span>
<span style="font-size:9.111;">19:59:17</span>
</nobr></div>';
SET @i1 = CharIndex('<nobr>',@text,1)
SET @i2 = CharIndex('</nobr>',@text,1)
DECLARE @myDoc xml
SET @myDoc = SubString(@text,@i1,@i2-@i1+Len('</nobr>'))
SELECT T.c.value('.','varchar(20)')
FROM @myDoc.nodes('/nobr/span') T(c)
for xml path('')
XML_********-****-****-****-************
---------------------------
Printed:09-Jul-201419:59:17
------解决方案--------------------
declare @s varchar(max)
set @s= '<top:28.324;left:20.409;"><nobr>
<span style="font-size:9.111;">Printed:</span>
<span style="font-size:9.111;">09-Jul-2014</span>
<span style="font-size:9.111;">19:59:17</span>
</nobr></div>'
;with cte as
(
select row_number () over ( order by snum desc )as rn1,snum