SQL 截取字符串

[Liyp92]

<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

注意里面有换行的

[霜寒月冷]

不用正则可惜了

[Liyp92]

引用 29 楼 happyflystone 的回复:

--加一个分隔函数:
create  function F_split(
                @s varchar(8000),          --包含多个数据项的字符串
                @pos int,                 --要获取的数据项的位置
                @split varchar(10)        --数据分隔符
)RETURNS varchar(50)
AS
BEGIN
    IF @s IS NULL RETURN(NULL)
    DECLARE @splitlen int                --分隔符长度
    declare @temp varchar(1000)
    SELECT @splitlen=LEN(@split ) 
    WHILE @pos>1 AND charindex(@split,@s+@split)>0
    begin
        SELECT @pos=@pos-1,
            @s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')
    end
    if (charindex(@split,@s+@split)-1 > 0 ) and (charindex(@split,@s+@split)-1< len(@s))
    begin
        set @temp = REVERSE((nullif(left(@s,charindex(@split,@s+@split)-1),'')))
        set @temp = REVERSE(left(@temp,charindex('>";',@temp)-1))
    end
    else
        set @temp = ''
    
    RETURN @temp
END
GO

灰常感谢

[霜寒月冷]

引用 4 楼 u012072926 的回复:

[quote=引用 2 楼 chz415767975 的回复:] 这个参考下，不是最好的实现方式

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:

能处理成Printed:09-Jul-2014 19:59:17吗[/quote] 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 for xml path('') --19:59:1709-Jul-2014Printed:

[-狙击手-]

XML....

[Liyp92]

引用 5 楼 Tiger_Zhao 的回复:

-- 先得把 <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

只能用XML的方法？

[Liyp92]

引用 7 楼 chz415767975 的回复:

[quote=引用 4 楼 u012072926 的回复:] [quote=引用 2 楼 chz415767975 的回复:] 这个参考下，不是最好的实现方式

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:

能处理成Printed:09-Jul-2014 19:59:17吗[/quote] 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 for xml path('') --19:59:1709-Jul-2014Printed:[/quote]XML没弄过。。还有别的方法吗

[Liyp92]

引用 8 楼 happyflystone 的回复:

XML....

有别的方法吗

[-狙击手-]

引用 11 楼 u012072926 的回复:

引用 8 楼 happyflystone 的回复:

XML....

有别的方法吗

写一个函数，直接调用

--加一个分隔函数:
create  function F_split(
                @s varchar(8000),          --包含多个数据项的字符串
                @pos int,                 --要获取的数据项的位置
                @split varchar(10)        --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
    IF @s IS NULL RETURN(NULL)
    DECLARE @splitlen int                --分隔符长度
    SELECT @splitlen=LEN(@split+'a')-2
    WHILE @pos>1 AND charindex(@split,@s+@split)>0
        SELECT @pos=@pos-1,
            @s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')
    RETURN(nullif(left(@s,charindex(@split,@s+@split)-1),''))
END
GO
DECLARE @text varchar(max)

 
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>';
select dbo.F_split(replace(@text,'<span style="font-size:9.111;">','</span>'),2,'</span>')+
dbo.F_split(replace(@text,'<span style="font-size:9.111;">','</span>'),4,'</span>')+' '+
dbo.F_split(replace(@text,'<span style="font-size:9.111;">','</span>'),6,'</span>')

drop function F_split

[Liyp92]

引用 12 楼 happyflystone 的回复:

引用 11 楼 u012072926 的回复:

[quote=引用 8 楼 happyflystone 的回复:] XML....

有别的方法吗

写一个函数，直接调用

--加一个分隔函数:
create  function F_split(
                @s varchar(8000),          --包含多个数据项的字符串
                @pos int,                 --要获取的数据项的位置
                @split varchar(10)        --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
    IF @s IS NULL RETURN(NULL)
    DECLARE @splitlen int                --分隔符长度
    SELECT @splitlen=LEN(@split+'a')-2
    WHILE @pos>1 AND charindex(@split,@s+@split)>0
        SELECT @pos=@pos-1,
            @s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')
    RETURN(nullif(left(@s,charindex(@split,@s+@split)-1),''))
END
GO
DECLARE @text varchar(max)

 
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>';
select dbo.F_split(replace(@text,'<span style="font-size:9.111;">','</span>'),2,'</span>')+
dbo.F_split(replace(@text,'<span style="font-size:9.111;">','</span>'),4,'</span>')+' '+
dbo.F_split(replace(@text,'<span style="font-size:9.111;">','</span>'),6,'</span>')

drop function F_split

[/quote]写函数调用很好，因为我这一串字符是一个字段，<span style="font-size:9.111;">是不固定的。

[Liyp92]

我本来想这么做，就是<开始，>结束。就把这里的内容删除，就后留下到就是我要的。 就是不知道怎么实现。各位大神可以这样实现吗？

[-狙击手-]

把函数改一下，不固定也没关系　

[rfq]

declare @str nvarchar(1000) declare @strHZ nvarchar(max) set @strHZ='' set @str='<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 ctes as ( select snum,row_number() over(order by snum) as 序号 from ( select distinct charindex ('<span style="font-size:9.111;">',@str,number)+LEN('<span style="font-size:9.111;">') as snum from master.dbo.spt_values where type='p' ) as s ) ,ctee as ( select enum,row_number() over(order by enum) as 序号 from ( select distinct charindex ('</span>',@str,number) as enum from master.dbo.spt_values where type='p' ) as s ) select @strHZ=@strHZ+SUBSTRING(@str,a.snum,b.enum-a.snum) from ctes a inner join ctee b on a.序号=b.序号 and b.enum>0 print @strHZ

[Liyp92]

引用 15 楼 happyflystone 的回复:

把函数改一下，不固定也没关系　

怎么改呢？而且我是一整串，没办法判断它的位置。 我比较菜。

[-狙击手-]

--加一个分隔函数:
create  function F_split(
                @s varchar(8000),          --包含多个数据项的字符串
                @pos int,                 --要获取的数据项的位置
                @split varchar(10)        --数据分隔符
)RETURNS varchar(50)
AS
BEGIN
    IF @s IS NULL RETURN(NULL)
    DECLARE @splitlen int                --分隔符长度
    declare @temp varchar(1000)
    SELECT @splitlen=LEN(@split ) 
    WHILE @pos>1 AND charindex(@split,@s+@split)>0
        SELECT @pos=@pos-1,
            @s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'')
    set @temp = REVERSE((nullif(left(@s,charindex(@split,@s+@split)-1),'')))
    set @temp = REVERSE(left(@temp,charindex('>";',@temp)-1))
    RETURN @temp
END
GO
DECLARE @text varchar(max)
declare @t table(t varchar(1000))
insert @t select '<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>';
insert @t select '<top:28.324;left:20.409;"><nobr> 
<span style="font-size:12.11;">Printed:</span> 
<span style="font-size:34.111;">08-Jul-2014</span> 
<span style="font-size:56.111;">20:59:17</span> 
</nobr></div>';
select dbo.F_split( t ,1,'</span>')+dbo.F_split( t ,2,'</span>')+' '+dbo.F_split( t ,3,'</span>')
from @t


/*
-------------------------------------------------------------------------------------------------------------------------------------------------------
Printed:09-Jul-2014 19:59:17
Printed:08-Jul-2014 20:59:17

(2 行受影响)
*/
drop function F_split

[Liyp92]

引用 16 楼 rfq 的回复:

declare @str nvarchar(1000) declare @strHZ nvarchar(max) set @strHZ='' set @str='<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 ctes as ( select snum,row_number() over(order by snum) as 序号 from ( select distinct charindex ('<span style="font-size:9.111;">',@str,number)+LEN('<span style="font-size:9.111;">') as snum from master.dbo.spt_values where type='p' ) as s ) ,ctee as ( select enum,row_number() over(order by enum) as 序号 from ( select distinct charindex ('</span>',@str,number) as enum from master.dbo.spt_values where type='p' ) as s ) select @strHZ=@strHZ+SUBSTRING(@str,a.snum,b.enum-a.snum) from ctes a inner join ctee b on a.序号=b.序号 and b.enum>0 print @strHZ

我的<span style="font-size:9.111;">是不固定的。到时候要整个字段全部都执行截取的

[-狙击手-]

试试１８楼。。。

[walkeeper]

总感觉是没事找事做的样子，你领导是不是消遣你啊= =！前端用JQ拿节点不是很方便么……

[Liyp92]

引用 20 楼 happyflystone 的回复:

试试１８楼。。。

谢谢 happyflystone 大神。不过如果不超过

insert @t select '<top:28.324;left:20.409;"><nobr> 
<span style="font-size:12.11;">Printed:</span> 
</nobr></div>';

消息 537，级别 16，状态 2，第 14 行
传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

句就会报错了

[Liyp92]

引用 21 楼 walkeeper 的回复:

总感觉是没事找事做的样子，你领导是不是消遣你啊= =！前端用JQ拿节点不是很方便么……

哈哈，我看是。 昨天和同事三个在讨论，同事说可以在程序用正则来处理。可领导还是执意要在SQL处理，可能他有别的用处吧

[渃水]

可以倒是可以，但何必用sql来做呢！