0

I have one field in database and I am getting the multiple dates in the same field. I want to extract multiple dates from the string. Please help me on this String like:

"7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM - Request assigned to 7/3/2019 3:22 AM - PO123456 created"

  • 2
    What exactly do you need help with? It'll probably be easier to do client-side using regular expression matching. – mustaccio Oct 15 '19 at 16:42
2

With the string you have in your example, you can use STRING_SPLIT as the first step

DECLARE @tags NVARCHAR(400) = '7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM - Request assigned to 7/3/2019 3:22 AM - PO123456 created'  

SELECT value  
FROM STRING_SPLIT(@tags, '-')  
WHERE RTRIM(value) <> '';

Which gives results

value
7/3/2019 1:20 AM 
Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM 
Request assigned to 7/3/2019 3:22 AM 
PO123456 created

You could then use RIGHT and or TRIM to leave only the dates

Select RIGHT ( ' Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM ' , 18 ) 

Gives

7/3/2019 1:34 AM 

If you want them to be dates at the end, you would need to use CAST / CONVERT to change the string to date format.

| improve this answer | |
  • 1
    The docs say about STRING_SPLIT: The output rows might be in any order.... – Luuk Oct 15 '19 at 19:38
  • @Luuk the documents also say "You can override the final sort order by using an ORDER BY clause on the SELECT statement (ORDER BY value)." but regardless your answer is a much better solution then mine. – James Jenkins Oct 16 '19 at 9:55
  • But the question is what to use for sorting? 😉 – Luuk Oct 16 '19 at 17:03
  • @Luuk I don't see sort in the question just extract, but there is not much info in the question, so what they really need in the end is anyone's guess. P.S. I have never really looked at a reclusive CTE before, dissecting your query has been very educational to me, thanks for posting it. – James Jenkins Oct 16 '19 at 17:12
3
+100

dbfiddle

DECLARE @string VARCHAR(MAX) = '7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM - Request assigned to 7/3/2019 3:22 AM - PO123456 created';

WITH abc(i,x,y) AS (
  SELECT 
    1 as i,
    @string as X,
    @string as Y
  UNION ALL
  SELECT 
    i+1,
    SUBSTRING(Y,1,CHARINDEX(' ',Y)),
    SUBSTRING(Y,CHARINDEX(' ',Y)+1,1024)
  from abc 
  where X<>'' 
  )
select TRY_CAST(CONCAT(a1.X, a2.X , a3.X) as DATETIME) ValidDate
from abc a1
inner join abc a2 on a2.i=a1.i+1
inner join abc a3 on a3.i=a1.i+2
where not TRY_CAST(CONCAT(a1.X, a2.X , a3.X) as DATETIME) is null;

output:

ValidDate
03/07/2019 01:20:00
03/07/2019 01:34:00
03/07/2019 03:22:00

Using LAG, or LEAD, example with dbfiddle does not seem to be less expensive than the INNER JOIN's. Results show almost equal times..

Below the example with LAG:

DECLARE @string VARCHAR(MAX) = '7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM - Request assigned to 7/3/2019 3:22 AM - PO123456 created';

WITH abc(i,x,y) AS (
  SELECT 
    1 as i,
    @string as X,
    @string as Y
  UNION ALL
  SELECT 
    i+1,
    SUBSTRING(Y,1,CHARINDEX(' ',Y)),
    SUBSTRING(Y,CHARINDEX(' ',Y)+1,1024)
  from abc 
  where X<>'' 
  )
SELECT * FROM (
    SELECT 
        TRY_CAST(concat(lag(a1.x,2) over (order by a1.i), lag(a1.x,1) over (order by a1.i), a1.x) as DATETIME) ValidDate
    from abc a1
    ) x
where not x.ValidDate is null;
| improve this answer | |
  • 1
    Would suggest TRY_CONVERT rather than TRY_CAST, so the date format is explicit via the style parameter. – Paul Holmes Oct 18 '19 at 11:07
  • 1
    Nice. May need to consider MAXRECURSION, depending on the input. – Michael Green Oct 18 '19 at 12:00
  • 1
    Using LAG (or LEAD) to get the sub-strings for the CONCAT is likely to produce a less costly plan. The three-way self-join will reduce to a scan + spool. – Michael Green Oct 18 '19 at 12:45
  • The fact that the LAG example is pretty almost equal in performance might be the low number of results from splitting this string on a space (22). – Luuk Oct 18 '19 at 17:47
  • Interesting. For me (2017 CU16 Dev Ed.) statistics IO and stats time consistently show the multi-join as 2-3ms elapsed and 417 logical reads; lag shows 0-1ms elapsed and 140 logical reads. For interactive use they're indistinguishable. Maybe for large batch runs, or as you say for longer strings, there may be an appreciable difference. – Michael Green Oct 19 '19 at 10:02
1

James Jenkin's Method is my favorite

With a couple tiny tweaks, you can use the isdate() function to test if it is a valid date, but since it also picks up the times, you can further qualify the results by length to end up with only the dates:

    DECLARE @tags NVARCHAR(400) = '7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM - Request assigned to 7/3/2019 3:22 AM - PO123456 created'  

    SELECT value  
    FROM STRING_SPLIT(@tags, ' ')  
    WHERE RTRIM(value) <> ''
    and isdate(value) = 1
    and len(value) = 8

you get the right result:

    value
    7/3/2019
    7/3/2019
    7/3/2019
| improve this answer | |
  • Hmmm .. len(value)=8 is not going to work for the last two thirds of each month, or any time after September. – Michael Green Oct 19 '19 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.