CODEWITHSUNDEEP
sql-servert-sql
Andrew Jocelyn
Andrew Jocelyn

Reputation: 579

How can I extract a string in SQL similar to a regex match?

For example I want to remove '(%)' off the end of the strings below:

select '07777 123456(s)' as [Number1]
select '07777 456789(p)' as [Number2]
select '+44 (0)7777 123456' as [Number3]

-- RESULTS
----------
-- 07777 123456
-- 07777 456789
-- +44 (0)7777 123456

Upvotes: 0

Views: 121

Answers (1)

Thom A
Thom A

Reputation: 95567

SQL Server doesn't support REGEX (and therefore Regex replacement), but you can use it's LIKE operator:

WITH CTE AS(
    SELECT '07777 123456(s)' AS Number
    UNION ALL
    SELECT '07777 456789(p)'
    UNION ALL
    SELECT '+44 (0)7777 123456')
SELECT CASE WHEN Number LIKE '%(_)' THEN LEFT(Number, LEN(Number) -3) ELSE Number END
FROM CTE;

Upvotes: 1

Related Questions