如何只提取模式字符串之前的数据

Question

如何仅提取CX_EduDegree=???????之后的字符串并用空格替换%20。数据由空格分隔，并且从不在同一位置。

我试过使用带substring和replace的patidix。但我没有成功。

select top 5 clientid, extFields
from tblSYS_Clients
where ExtFields like '%CX_Edu%'
and ClientID in ('1633496','1633692','1453977','1657410','1584563','1655341','1632686','1352611','1484271','1361354') 

clientid    extFields
1352611 CX_CurrentJobStartDate=01/20/2001  CX_CurrentJobHours=40  CX_SupervisorName=Rhonda%20Kaiser  CX_EduDegree=BS%20in%20Nursing  CX_SupervisorPhone=970-495-8100
1361354 CX_CurrentJobStartDate=06/20/1997  CX_CurrentJobHours=30  CX_SupervisorName=Georgia%20Chapin  CX_SupervisorPhone=702-616-5632  CX_EduDegree=MS/MA%20%20in%20Nursing
1453977 CX_CurrentJobStartDate=08/20/1990  CX_CurrentJobHours=40  CX_SupervisorName=Jan%20Rasco  CX_SupervisorPhone=281-631-8789  CX_EduDegree=Diploma
1484271 CX_CurrentJobStartDate=01/01/2011  CX_CurrentJobHours=40  CX_SupervisorName=Kay%20Hix  CX_SupervisorPhone=317-329-7209  CX_EduDegree=AD%20in%20Nursing
1584563 CX_CurrentJobStartDate=11/26/2006  CX_CurrentJobHours=40  CX_SupervisorName=PHILLIP%20MOISUK  CX_SupervisorPhone=916-453-4545  CX_EduDegree=BS%20in%20Nursing

我想看到的结果：

1633496 BS in Nursing 
1633692 BS in Nursing 
1453977 Diploma 
1657410 AD in Nursing 
1584563 BS in Nursing 
1655341 AD in Nursing 
1632686 BS in Nursing 
1352611 BS in Nursing 
1484271 AD in Nursing 
1361354 MS/MA in Nursing 

Answer 1

使用charindex、reverse和substring的组合。如果CX_EduDegree=总是出现在字符串的末尾，这将起作用。

带有样本数据的演示

select clientid,
substring(
          extfields
          ,charindex('CX_EduDegree=',extFields)+len('CX_EduDegree=')
          ,charindex(' ',extFields)
         ) as extfield
from tblSYS_Clients
where ExtFields like '%CX_Edu%'
and ClientID in ('1633496','1633692','1453977','1657410','1584563','1655341','1632686','1352611','1484271','1361354') 

Answer 2

使用此构造：

select clientid, replace(SUBSTRING(extFields,
   PATINDEX('%CX_EduDegree=%',extFields)+13,
      charindex(' ', extFields+' ',
          PATINDEX('%CX_EduDegree=%',extFields)) - 
             PATINDEX('%CX_EduDegree=%',extFields) - 13),'%20',' ') ex 
from  
(values 
(1352611, 'CX_CurrentJobStartDate=01/20/2001  CX_CurrentJobHours=40  CX_SupervisorName=Rhonda%20Kaiser  CX_EduDegree=BS%20in%20Nursing  CX_SupervisorPhone=970-495-8100'), 
(1361354, 'CX_CurrentJobStartDate=06/20/1997  CX_CurrentJobHours=30  CX_SupervisorName=Georgia%20Chapin  CX_SupervisorPhone=702-616-5632  CX_EduDegree=MS/MA%20%20in%20Nursing'), 
(1453977, 'CX_CurrentJobStartDate=08/20/1990  CX_CurrentJobHours=40  CX_SupervisorName=Jan%20Rasco  CX_SupervisorPhone=281-631-8789  CX_EduDegree=Diploma'), 
(1484271, 'CX_CurrentJobStartDate=01/01/2011  CX_CurrentJobHours=40  CX_SupervisorName=Kay%20Hix  CX_SupervisorPhone=317-329-7209  CX_EduDegree=AD%20in%20Nursing'), 
(1584563, 'CX_CurrentJobStartDate=11/26/2006  CX_CurrentJobHours=40  CX_SupervisorName=PHILLIP%20MOISUK  CX_SupervisorPhone=916-453-4545  CX_EduDegree=BS%20in%20Nursing') 
) t(clientid,    extFields)

结果：

clientid    ex
1352611 BS in Nursing
1361354 MS/MA  in Nursing
1453977 Diploma
1484271 AD in Nursing
1584563 BS in Nursing

为了更好地理解代码中的一些注释。正如您所看到的，charindex也起作用。结果是一样的。

select clientid, 
replace(
SUBSTRING(extFields,--string to work with
   charindex('CX_EduDegree=',extFields)+13, --start position; 13 is length of CX_EduDegree=
      charindex(' ', extFields+' ', --searching end position +' ' to make sure that space exists
          charindex('CX_EduDegree=',extFields) -- start searching ' ' after this position
          ) --end position found
             - charindex('CX_EduDegree=',extFields) - 13) -- calculate length
             ,'%20',' ') ex --final touch - remove ugly %20 
from  
(values 
(1352611, 'CX_CurrentJobStartDate=01/20/2001  CX_CurrentJobHours=40  CX_SupervisorName=Rhonda%20Kaiser  CX_EduDegree=BS%20in%20Nursing  CX_SupervisorPhone=970-495-8100'), 
(1361354, 'CX_CurrentJobStartDate=06/20/1997  CX_CurrentJobHours=30  CX_SupervisorName=Georgia%20Chapin  CX_SupervisorPhone=702-616-5632  CX_EduDegree=MS/MA%20%20in%20Nursing'), 
(1453977, 'CX_CurrentJobStartDate=08/20/1990  CX_CurrentJobHours=40  CX_SupervisorName=Jan%20Rasco  CX_SupervisorPhone=281-631-8789  CX_EduDegree=Diploma'), 
(1484271, 'CX_CurrentJobStartDate=01/01/2011  CX_CurrentJobHours=40  CX_SupervisorName=Kay%20Hix  CX_SupervisorPhone=317-329-7209  CX_EduDegree=AD%20in%20Nursing'), 
(1584563, 'CX_CurrentJobStartDate=11/26/2006  CX_CurrentJobHours=40  CX_SupervisorName=PHILLIP%20MOISUK  CX_SupervisorPhone=916-453-4545  CX_EduDegree=BS%20in%20Nursing') 
) t(clientid,    extFields)

你觉得这个解决方案可以接受吗？

Answer 3

使用XML数据类型的一种方法

SELECT  clientid,
        REPLACE(t.cx.value('(r/@CX_EduDegree)[1]', 'varchar(100)'),'%20',' ') AS degree
FROM    (
    SELECT  clientid, 
            CAST('<r ' + REPLACE(REPLACE(extFields,'=','="') , '  ','"/><r ') + '"></r>') AS XML) cx
    FROM    tblSYS_Clients
    WHERE   clientid in ('1633496','1633692','1453977','1657410','1584563','1655341','1632686','1352611','1484271','1361354') 
) t

与字符串操作查询相比，xml是一个相当慢的查询。

你可以做的另一件事是使用字符串拆分器，比如HERE

这里有一个使用Jeff Moden的DelimitedSplit8K函数的例子。

SELECT  t1.clientid,
        REPLACE(t2.Item,'CX_EduDegree=','')
FROM    tblSYS_Clients t1
        CROSS APPLY (
            SELECT REPLACE(t.Item, '%20', ' ') Item 
            FROM dbo.DelimitedSplit8K(t1.extFields, '  ') t 
            WHERE t.Item LIKE 'CX_EduDegree%' 
        ) t2
WHERE   clientid in ('1633496','1633692','1453977','1657410','1584563','1655341','1632686','1352611','1484271','1361354') 

仍然没有这里的其他领域那么快，但可能更容易理解或让您更容易了解其他领域。

Answer 4

字符串操作在SQL Server中有点麻烦。我喜欢使用outer apply:

select c.clientid, c.extFields, x2.x2
from tblSYS_Clients c outer apply
     (select stuff(c.ExtFields, 1, charindex('CX_edu', c.ExtFields), '') as x1
     ) x outer apply
     (select replace(left(x.x1, charindex(' ', x1)), '%20', ' ') as x2
     ) x2
where c.ExtFields like '%CX_Edu%' and
      c.ClientID in ('1633496', '1633692', '1453977', '1657410', '1584563', '1655341', '1632686', '1352611', '1484271', '1361354') ;

注意：我认为以上内容会起作用。SQL Server不保证在outer apply子查询之前处理where。在实践中，我认为确实如此，所以过滤是为了确保子字符串在ExtFields中。如果没有，使用case可以很容易地解决这个问题，但这会使答案变得有点复杂。