sql - 如何返回特定的字符串

Question

我有一个包含数千行数据的表，与此类似。

note
----
LB MN IM 12 18 20  CIN # EW80851R This is tJ
ified KGM nteal icne cinac12345T Cannot locate
NCR  Last verified 06 05 14 cin number ty56478P for the front
OD 00612  Last Verified cin#ad89521Y Me side C 05 05 14
SPC 0   VERIFIED PD IMB cin PU12301R  Last Verified 
PC PO CON FC  D Verified 02/29/2016 No Copy  CIN#FG62301F

每一行都包含单词 cin，后跟几个字符。

例如:CIN#EW80851R，cinac12345T，cin 编号 ty56478P，cin#ad89521Y，cin PU12301R，CIN#FG62301F

我将如何编写一个只返回的查询:

note
----
cinEW80851R
cinac12345T
cinty56478P
cinad89521Y
cinPU12301R
CINFG62301F

Answer 1

借助 XML、字符串解析器和交叉应用的一点帮助，您可以处理整个数据集。

你会注意到我添加了第二条记录

Declare @YourTable table (ID int,Note varchar(max))
Insert into @YourTable values
(1,'LB MN IM 12 18 20  CIN # EW80851R This is tJ ified KGM nteal icne cinac12345T Cannot locate NCR  Last verified 06 05 14 cin number ty56478P for the front OD 00612  Last Verified cin#ad89521Y Me side C 05 05 14 SPC 0   VERIFIED PD IMB cin PU12301R  Last Verified PC PO CON FC  D Verified 02/29/2016 No Copy  CIN#FG62301F'),
(2,'L This is tJ KGM teal icne Last verified 06 05 14 for the front OD 00612  Last Verified cin#ZZ89256Y Me side C 05 05 14 SPC 0   VERIFIED PD IMB cin ZZPU12301R  Last Verified PC PO CON FC  D Verified 02/29/2016 No Copy  CIN#ZZ62301F')

-- Create a Mapping/Normaization Table
Declare @Map table (MapSeq int,MapFrom varchar(25),MapTo varchar(25))
Insert into @Map values (1,char(13),' '),(2,char(10),' '),(3,' cin number ',' cin'),(4,' cin # ',' cin'),(5,' cin#',' cin'),(6,' cin ',' cin')

-- Convert your Base Data to XML
Declare @XML XML,@String varchar(max)
Set @XML = (Select KeyID=ID,String=+' '+Note+' ' from @YourTable For XML RAW)

-- Convert XML to Varchar(max) and Apply Global Search & Replace 
Select @String = cast(@XML as varchar(max))
Select @String = Replace(@String,MapFrom,MapTo) From @Map Order by MapSeq

-- Convert Back to XML
Select @XML = cast(@String as XML)

-- Generate Final Results
Select A.ID
      ,CIN  = B.Key_Value
 From (
        Select ID        = t.col.value('@KeyID', 'int')
              ,NewString = t.col.value('@String', 'varchar(max)')
         From  @XML.nodes('/row') AS t (col)
      ) A
 Cross Apply (Select * from [dbo].[udf-Str-Parse](A.NewString,' ') where Key_Value like 'cin%') B

返回

ID  CIN
1   cinEW80851R
1   cinac12345T
1   cinty56478P
1   cinad89521Y
1   cinPU12301R
1   cinFG62301F
2   cinZZ89256Y     << Dummy Record
2   cinZZPU12301R   << Dummy Record
2   cinZZ62301F     << Dummy Record

UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--       Select * from [dbo].[udf-Str-Parse]('id26,id46|id658,id967','|')
--       Select * from [dbo].[udf-Str-Parse]('hello world. It. is. . raining.today','.')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select Key_Value = ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End