Pattern Matching SQL

• rka

  SSCommitted

  Points: 1599

  More actions

  October 29, 2014 at 7:52 pm

  #300527

  Can someone help me to extract values from the following sample data using Pattern Matching?

  Create Table dbo.Webs

  (

  FullURL nvarchar(255)

  )

  Go

  Insert Into dbo.Webs

  Select 'PWA/ENV0103'

  Union

  Select 'PWA/FBP.004'

  Union

  Select 'PWA/IP.CE361886'

  Union

  Select 'PWA/OFS.CE366077'

  Union

  Select 'PWA/NWK0108'

  Union

  Select 'PWA/TK.2347'

  Union

  Select 'PWA/DRHTest2'

  Union

  Select 'PWA/IDTS.CE363798'

  Union

  Select 'PWA/or.01795'

  Union

  Select 'PWA/ICT0103'

  Union

  Select 'PWA/OR.00708'

  Union

  Select 'PWA/TS_NetworkProperty_CP01495'

  Union

  Select 'PWA/CP.01420'

  Union

  Select 'PWA/OR.01378/OR.01391'

  Union

  Select 'PWA/FBP.365113'

  Union

  Select 'PWA/CP.96xxx'

  I want the Expected Values as per the attachment

  Thanks

• djj

  SSCoach

  Points: 18831

  More actions

  October 30, 2014 at 8:34 am

  #1755793

  Why are the two not expecting result?

• Eirikur Eiriksson

  SSC Guru

  Points: 182901

  More actions

  October 30, 2014 at 11:04 am

  #1755867

  Some additional knowledge/information/logic is needed to complete this as djj mentioned, here is some quick code to get you started.

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  IF OBJECT_ID('dbo.Webs') IS NULL

  BEGIN

  Create Table dbo.Webs

  (

  FullURL nvarchar(255)

  );

  Insert Into dbo.Webs

  Select 'PWA/ENV0103' UNION ALL Select 'PWA/FBP.004' UNION ALL Select 'PWA/IP.CE361886' UNION ALL Select 'PWA/OFS.CE366077' UNION ALL

  Select 'PWA/NWK0108' UNION ALL Select 'PWA/TK.2347' UNION ALL Select 'PWA/DRHTest2' UNION ALL Select 'PWA/IDTS.CE363798' UNION ALL

  Select 'PWA/or.01795' UNION ALL Select 'PWA/ICT0103' UNION ALL Select 'PWA/OR.00708' UNION ALL Select 'PWA/TS_NetworkProperty_CP01495' UNION ALL

  Select 'PWA/CP.01420' UNION ALL Select 'PWA/OR.01378/OR.01391' UNION ALL Select 'PWA/FBP.365113' UNION ALL

  Select 'PWA/CP.96xxx';

  END

  SELECT

  W.FullURL

  ,SUBSTRING(W.FullURL,NULLIF(CHARINDEX('/',W.FullURL,1),0) + 1,LEN(W.FullURL))

  FROM dbo.Webs W

  Results

  FullURL

  ---------------------------------- ----------------------------

  PWA/ENV0103 ENV0103

  PWA/FBP.004 FBP.004

  PWA/IP.CE361886 IP.CE361886

  PWA/OFS.CE366077 OFS.CE366077

  PWA/NWK0108 NWK0108

  PWA/TK.2347 TK.2347

  PWA/DRHTest2 DRHTest2

  PWA/IDTS.CE363798 IDTS.CE363798

  PWA/or.01795 or.01795

  PWA/ICT0103 ICT0103

  PWA/OR.00708 OR.00708

  PWA/TS_NetworkProperty_CP01495 TS_NetworkProperty_CP01495

  PWA/CP.01420 CP.01420

  PWA/OR.01378/OR.01391 OR.01378/OR.01391

  PWA/FBP.365113 FBP.365113

  PWA/CP.96xxx CP.96xxx