Using PATINDEX to find times within text in SQL Server using multiple formats

Question

I need to use a regular expression which returns the time of format 12:43 AND 1:33, I tried the following, and each one returns the desired results, how can I combine both so SQL can return either the first OR the second :

set @reg = '[0-9]:[0-5][0-9]'
set @reg = '[0-1][0-2]:[0-5][0-9]'

What I have tried:

Declare @reg nvarchar(100) 
set @reg = '[0-9]:[0-5][0-9]' 
--set @reg = '[0-1][0-2]:[0-5][0-9]' 

select remarks, 
       substring(remarks,PATINDEX('%' + @reg + '%',remarks) ,5), len(PATINDEX('%' + @reg + '%',remarks)) 
from infraction 
where remarks like '%' + @reg + '%'

As you might have seen, I need to extract time from a text field saved in a table, what is the way to return ONLY the extracted time — Hayan Al Mamoun, Jun 05 '19 at 17:31

TomNash · Accepted Answer · 2019-06-06T13:53:29.573

You'll want to find any row where either pattern exists, then apply the appropriate SQL limited "regex" based on which pattern matches. The HH:MM one is more limiting than H:MM so we use that to check.

CREATE TABLE #infraction (
  Comment VARCHAR(100)
)

INSERT INTO #infraction VALUES ('time of 12:35 incident')
INSERT INTO #infraction VALUES ('time of 1:34 incident');

DECLARE @reg NVARCHAR(100) = '[0-9]:[0-5][0-9]'
DECLARE @reg2 NVARCHAR(100) = '[0-1][0-2]:[0-5][0-9]'

SELECT
    Comment,
    IIF(PATINDEX('%' + @reg2 + '%', Comment) = 0,
        SUBSTRING(Comment, PATINDEX('%' + @reg + '%', Comment), 4),
        SUBSTRING(Comment, PATINDEX('%' + @reg2 + '%', Comment), 5)
    )
FROM
    #infraction 
WHERE
    Comment LIKE '%' + @reg + '%'
    or
    Comment LIKE '%' + @reg2 + '%';

Returns:

12:35
1:34

SQL Fiddle

Using PATINDEX to find times within text in SQL Server using multiple formats

1 Answers1