Server :从string选择整数到新表

Question

我需要从不同格式的字符串(urls)中提取6-7个数字的文档ID，我希望这样做可以作为数据库查询。

背景：

基本上，我需要基于现有url中标识的docid构建一个新的url，这些都在EXTERNAL_REF数据库列中。

例如：

http://*******/root/wrks/stores/default/default/pdf/docid/999762/dw_get

会变成

https://******/Prod/Anywhere/Web/Prod/ShowFunction/page?sk.DocumentId=999762&suite=ADT

示例源数据：

default/default/orig/docid/999980/pageno/1/dw_get
default/default/orig/docid/999986/pageno/1/dw_get
default/yyy/orig/docid/1000001/pageno/67/dw_get
78/default/orig/docid=1000480/pageno/1/dw_get
default/default/orig/docid=1000516/pageno/767/dw_get
default/default/orig/docid=1000527/pageno/1/dw_get
default/xxx/pdf/docid=999762/dw_get
default/default/orig/docid/1000581/pageno/1/dw_get
679/default/pdf/docid/999761/dw_get
default/default/orig/docid/1000590/pageno/1/dw_get
default/default/orig/docid/985747/dw_get

所需结果：

999980
999986
1000001
1000480
1000516
1000527
999762
1000581
999761
1000590
985747

Answer 1

您可以使用以下方法

DECLARE @temp TABLE
(
      string NVARCHAR(150)
)

INSERT INTO @temp (string)
VALUES 
    ('default/default/orig/docid/999980/pageno/1/dw_get'),
('default/default/orig/docid/999986/pageno/1/dw_get'),
('default/yyy/orig/docid/1000001/pageno/67/dw_get'),
('78/default/orig/docid=1000480/pageno/1/dw_get'),
('default/default/orig/docid=1000516/pageno/767/dw_get'),
('default/default/orig/docid=1000527/pageno/1/dw_get'),
('default/xxx/pdf/docid=999762/dw_get'),
('default/default/orig/docid/1000581/pageno/1/dw_get'),
('679/default/pdf/docid/999761/dw_get'),
('default/default/orig/docid/1000590/pageno/1/dw_get'),
('default/default/orig/docid/985747/dw_get')

-- get the data after docid in the string
;with report as(
select substring(string,CHARINDEX('docid',string,0)+6,len(string)) as string
from @temp
)
-- get the the number up to next '/'
select substring(string,0,charindex('/',string,0))
from report

对于使用一个查询，可以将两个脚本合并在同一个查询中。

select substring(substring(string,charindex('docid',string,0)+6,len(string)),0,
                 charindex('/',substring(string,
                 charindex('docid',string,0)+6,len(string)),0))
from @temp

这里是一个演示

希望这能帮到你

Answer 2

在Server中，字符串操作很笨拙，但是您可以：

select v2.*
from t cross apply
     (values (stuff(url, 1, patindex('%[0-9][0-9][0-9][0-9][0-9][0-9]%', t.url) - 1, '') )
     ) v1(url1) cross apply
     (values (left(v1.url1, charindex('/', v1.url1) - 1))
     ) v2(url2);

这里是一个rextester。

Answer 3

关键是使用char索引和子字符串来实现您想要的。这里有一种方法。不一定是最好的方法..。但还是..。我已经把它按步骤分解了。这样你就能看到发生了什么。

declare @string nvarchar (500) = 'default/default/orig/docid=999980/pageno/1/dw_get'
;with mycte as (
select 
@string as original_string
,   charindex('docid/',@string) [doc/]
,   charindex('docid=',@string) [doc=]
,case when 
        charindex('docid/',@string) <>0 then substring(@string,charindex('docid/',@string),100) 
            else  substring(@string,charindex('docid=',@string),100)  end as stripped
,replace(replace(case when 
        charindex('docid/',@string) <>0 then substring(@string,charindex('docid/',@string),100) 
            else  substring(@string,charindex('docid=',@string),100)  end,'docid/',''),'docid=','') remove_docid
)

Select 
        original_string
        ,[doc/]
        ,[doc=]
        ,stripped
        ,remove_docid
        ,left(remove_docid,CHARINDEX('/',remove_docid)-1) as document_no

 from mycte