REGEXP_SUBSTR equivalent in SQL Server

Question

I have an Oracle query using

SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',',[^,]+,') 
FROM DUAL;

It returns the output as below:

, Redwood Shores,

I am trying to get the exact same result in SQL Server. I tried to do it as below

 substring(text, patindex, length)

But I struggled with length specification.

Can you please let me know how I can achieve this is in SQL Server?

SQL Server doesn't have regular expressions. You can use the [STRING_SPLIT](https://msdn.microsoft.com/en-us/library/mt684588.aspx) table-valued function to split a string and return its parts as separate rows — Panagiotis Kanavos, Jan 02 '17 at 09:49
i was looking for the same thing. Didn't see any equivalence to that either! We should suggest Micro-soft staff to develop THAT! — Jenna Leaf, Apr 11 '19 at 16:37

score 2 · Answer 1 · edited Nov 24 '20 at 22:25

You can try below query

WITH dual AS
(
 SELECT '500 Oracle Parkway, Redwood Shores, CA' AS st
)
SELECT SUBSTRING(REVERSE(SUBSTRING(REVERSE(st),
                                   CHARINDEX(',', REVERSE(st)),
                                   LEN(st))),
                 CHARINDEX(',', st),
                 LEN(st)) str_value
  FROM dual

Below is the result which I have got

Note: with clause is just for data preparation. Just make use of the substring part in select statement

score 1 · Answer 2 · answered Jan 02 '17 at 10:10

Try this:

Declare @test varchar(max)='500 Oracle Parkway, Redwood Shores, CA'
select reverse(substring(reverse(substring(@test,charindex(',',@test),len(@test))),
        charindex(',',reverse(substring(@test,charindex(',',@test),len(@test)))),
        len(substring(@test,charindex(',',@test),len(@test)))))

REGEXP_SUBSTR equivalent in SQL Server

2 Answers2