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

Answer 1

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