Can you do a regex in the select statement on SQL Server?

I can see you can do like in the where clause, but can you do a regex in a select statement? I have rows with something like following:

/sites/xxxx-588-xxxxxxxx/Delte dokumenter/xxxx/xxxx/xxx/file.pdf

And I need to do a select that always get the value between /sites/ and after the next /. So here it would be xxxx-588-xxxxxxxx. So if you could so something like select regex(columnname, ‘my regex’) from table …. Is that possible?

Answer

We can use CHARINDEX here:

SELECT
    path,
    SUBSTRING(path,
              CHARINDEX('/sites/', path) + 7,
              CHARINDEX('/', path, CHARINDEX('/sites/', path) + 7) -
                  CHARINDEX('/sites/', path) - 7) AS component
FROM yourTable;

Demo

The idea here is to take the following substring:

/sites/xxxx-588-xxxxxxxx/Delte dokumenter/xxxx/xxxx/xxx/file.pdf
       ^    ...        ^

We do this using CHARINDEX by finding the position of /sites/, and then also finding the occurrence of the next / after /sites.