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?
Advertisement
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;
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
.