CODEWITHSUNDEEP
sql-serveroracle-databaseregexp-substr
pratham gn
pratham gn

Reputation: 105

REGEXP_SUBSTR equivalent in SQL Server

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?

Upvotes: 2

Views: 8567

Answers (2)

Viki888
Viki888

Reputation: 2774

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

enter image description here

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

Upvotes: 2

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

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

Upvotes: 1

Related Questions