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?

Answer 1

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)))))

Answer 2

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