0

I am trying to do a select with a regex. I have seen how to do this in where clauses on multiple posts but that is not what I want. What I want is to select a column, then apply a regex, then display it as something else (in this case timecodes).

I thought there was a regular expression function, but I only seem to find PATINDEX, which just tells me where the pattern starts. Is there a SQL function that I can use to apply a regex to something?

Something like:

SELECT PATINDEX('/^(\d{2})(\d{2})(\d{2})(\d{2})$/', TIMECODE) AS timecode 
FROM INSTANCE

Except it returns the column with the regex applied

EDIT

Just so there is more clarity, I want to change how the data is returned. Say I have this time in millis: 1539885525171. I want to have that returned as the time 17:59:29.

I know this regex is not right, but for the sake of an example, I want to apply the expression '/^(\d{2})(\d{2})(\d{2})(\d{2})$/' to 1539885525171 so that it returns 17:59:29

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Soatl
  • 10,224
  • 28
  • 95
  • 153
  • 2
    What to do want to mean by "apply a regex" Can you show us data sample, expected result, and regex rules? – DanB Oct 18 '18 at 17:57
  • 1
    Full Regex isn't supported in sql server. There are limited similarities with `PATINDEX` and `LIKE`. Sample data and expected output would help determine what you are trying to do. – S3S Oct 18 '18 at 17:57
  • Ok. See the edit please – Soatl Oct 18 '18 at 18:01
  • 2
    so like [this](https://stackoverflow.com/a/12757846/6167855) ? – S3S Oct 18 '18 at 18:07

1 Answers1

0

If You only need a time You can do this way

declare @UTC bigint = 1539885525171 
SELECT convert(nvarchar(8)
             ,DATEADD(MILLISECOND, @UTC % 1000, DATEADD(SECOND, @UTC / 1000, '19700101'))
             ,8)
DimaSUN
  • 921
  • 8
  • 13
  • and even more simple `declare @UTC bigint = 1539885525171 ; SELECT convert(nvarchar(20), DATEADD(SECOND, @UTC / 1000, '19700101'),8)` – DimaSUN Oct 18 '18 at 20:07