Extract substring in SQL Statement

• Tamrak

  SSC Eights!

  Points: 902

  More actions

  February 12, 2015 at 6:15 pm

  #315272

  I tried to extract the information in a field

  Samples of the data:

  "Current Date updated ( 12/31/2015 -> 12/31/2016 )"

  "Status updated ( Open -> Should be Closed )"

  From example 1 ==> need outputs of " 12/13/2015" and "12/31/2016";

  example 2 --> need outputs of "Open" and "Should be Closed"

  These are what all records look like:

  1. It contains the word, "updated".

  2. There is a space after the last character of updated. Then, there is a space before open parenthesis. Then, there is a space before getting the required word. Example: "updated ( xxxxx" (xxxxx is a required extract word.)

  3. The entire word(s) should be captured before the "->" sign (after the open parenthesis)

  4. The last part is to captured everything after the "->" but before the close parenthesis ")"

  Can anyone help? Let me know whether I use the correct function as well. Thank you.

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  February 12, 2015 at 7:33 pm

  #1777388

  Tamrak (2/12/2015)

  ---

  I tried to extract the information in a field

  Samples of the data:

  "Current Date updated ( 12/31/2015 -> 12/31/2016 )"

  "Status updated ( Open -> Should be Closed )"

  From example 1 ==> need outputs of " 12/13/2015" and "12/31/2016";

  example 2 --> need outputs of "Open" and "Should be Closed"

  These are what all records look like:

  1. It contains the word, "updated".

  2. There is a space after the last character of updated. Then, there is a space before open parenthesis. Then, there is a space before getting the required word. Example: "updated ( xxxxx" (xxxxx is a required extract word.)

  3. The entire word(s) should be captured before the "->" sign (after the open parenthesis)

  4. The last part is to captured everything after the "->" but before the close parenthesis ")"

  Can anyone help? Let me know whether I use the correct function as well. Thank you.

  You say you tried and wanted to know if you used the correct functions but I didn't see any code in your post.

  Details are in the code.

  --===== Here's the test table with your samples.

  -- Please see the first link under "Helpful Links"

  -- in my signature line below for future posts.

  SELECT *

  INTO #Testtable

  FROM (

  SELECT 'Current Date updated ( 12/31/2015 -> 12/31/2016 )' UNION ALL

  SELECT 'Status updated ( Open -> Should be Closed )'

  )d(SomeString)

  ;

  --===== Here's one answer... I left all columns exposed so you

  -- could see the results of the cCAs (Cascading Cross Applies)

  SELECT *

  FROM #TestTable

  CROSS APPLY (SELECT CHARINDEX('( ',SomeString)+2

  ,CHARINDEX(' -> ',SomeString)

  ,CHARINDEX(' )',SomeString)

  )ca1(Delim1,Delim2,Delim3)

  CROSS APPLY (SELECT SUBSTRING(Somestring,Delim1,Delim2-Delim1+1)

  ,SUBSTRING(SomeString,Delim2+4,Delim3-Delim2-3)

  )ca2(Output1,Output2)

  WHERE SomeString LIKE '%updated ( % -> % )'

  ;

  Here are the results.

  SomeString Delim1 Delim2 Delim3 Output1 Output2

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

  Current Date updated ( 12/31/2015 -> 12/31/2016 ) 24 34 48 12/31/2015 12/31/2016

  Status updated ( Open -> Should be Closed ) 18 22 42 Open Should be Closed

  (2 row(s) affected)

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)