Parse data from a memo SQL

Question

So basically I have several memos in a DB. I need to query through them and only grab each note in the memo that has a date before it. So if I had this for example:

abc def ghi jkl 9/1/17: mno pqr

I would only want to Grab the 9/1/17: mno pqr part

Some even have more entries after the first initial date like this:

abc def ghi jkl 9/1/17: mno pqr 9/2/17: stu vwx yz

Which in this case i would need both9/1/17: mno pqr and 9/2/17: stu vwx yz.

Possibly putting each value in a new row like so:

1: 9/1/17: mno pqr 2: 9/2/17: stu vwx yz

This way if it is out of order I can reorder them as needed.

Any help would be greatly appreciated! Thanks

So each row contains a single string with all that nonsense in the string, and you need to parse out chunks of it? Or am I misunderstanding? — Xedni, Oct 05 '17 at 22:07
Assuming all of your dates are formatted similarly, one way would be to use some sort of patindex parsing with a recursive CTE. — ZLK, Oct 05 '17 at 22:11
It is a 255 character field that I need to go through and get the first date followed by the data all the way to the next day. So for instance. `abcdefg 9/21/17 took the notes. 9/23/17 printed the notes.` Would have to look like this: `9/21/17 took the notes.` `9/23/17 printed the notes.` — Matt Synco, Oct 05 '17 at 22:19
Let's start with the rules for a "date": Is it always `month/day/year`? Is the year always two digits? Are month and day never zero-filled? How do you plan to "reorder" dates stored as text, e.g. "10/1/17" is less than "9/30/15"? And the standards: What have you tried? What have you researched? — HABO, Oct 06 '17 at 00:01
@HABO makes a good point. You're going to find that these dates are entered inconsistently. You're going to get an algorithm that get 50% out correctly. Then you'l' need to add rules to improve that.. until you discover that people have been entering dates in M/D/Y format and D/M/Y format... then D-M-Y then Y-M-D. Just sayin.. whatever you have posted above will not be the full story. — Nick.Mc, Oct 06 '17 at 02:37

score 0 · Accepted Answer · answered Oct 05 '17 at 22:17

You can use charindex or patindex

Assumptions, 1. Your text will not contain '/' other than in the date 2. Your date will have the separator as '/'

The below query will return the first occurrence of '/' in your string.

SELECT PATINDEX('%/%', yourcolumn) 
  FROM yourtable

The value returned from the above query - 2 will be the start position of your date. The length of the whole string - the value returned from the above query will be the length of your output.

SELECT susbtring(yourcolumn, PATINDEX('%/%', yourcolumn) -2, 
         length(yourcolumn)-PATINDEX('%/%', yourcolumn))
  FROM yourtable

If you are looking to handle multiple date then you have to use recursive CTE

@MattSynco, please avoid to say thanks, the way to say thanks in stackoverflow is to mark the answer as correct and add a + to all the answer that helped you, this way you pay the time and the effort of the guy that answered the question — Victor Hugo Terceros, Oct 05 '17 at 22:59

score 0 · Answer 2 · answered Oct 06 '17 at 02:27

You could use PatternSplitCM (DDL for the function below). The solution would look like this (note that you need SQL Server 2012+ to run this because I'm using LEAD):

declare @string varchar(255) = 
'abcdefg 9/21/17 took the notes. 9/23/17 printed the notes. 9/21/17 took the notes. 9/23/17 printed the notes.'

select ItemNumber = concat(ItemNumber/2,':'), Item
from 
(
  select ItemNumber, item = item +' '+ LEAD(item, 1) OVER (ORDER BY itemNumber), [Matched]
  from dbo.PatternSplitCM(@string, '[0-9/]')
) ExtractDates
where [Matched] = 1;

Results

ItemNumber   Item
------------ ----------------------------
1:           9/21/17  took the notes. 
2:           9/23/17  printed the notes. 
3:           9/21/17  took the notes. 
4:           9/23/17  printed the notes.

The Function

-- PatternSplitCM will split a string based on a pattern of the form 
-- supported by LIKE and PATINDEX 
-- 
-- Created by: Chris Morris 12-Oct-2012 
ALTER FUNCTION [dbo].[PatternSplitCM]
(
       @List                VARCHAR(8000) = NULL
       ,@Pattern            VARCHAR(50)
) RETURNS TABLE WITH SCHEMABINDING 
AS    
RETURN
    WITH numbers AS (
      SELECT TOP(ISNULL(DATALENGTH(@List), 0))
       n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
      FROM
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n))
    SELECT
      ItemNumber = ROW_NUMBER() OVER(ORDER BY MIN(n)),
      Item = SUBSTRING(@List,MIN(n),1+MAX(n)-MIN(n)),
      [Matched]
     FROM (
      SELECT n, y.[Matched], Grouper = n - ROW_NUMBER() OVER(ORDER BY y.[Matched],n)
      FROM numbers
      CROSS APPLY (
          SELECT [Matched] = CASE WHEN SUBSTRING(@List,n,1) LIKE @Pattern THEN 1 ELSE 0 END
      ) y
     ) d
     GROUP BY [Matched], Grouper;

Parse data from a memo SQL

2 Answers2