0

How to use regex replace to ignore country code if it exists, I tried to use

select REGEXP_REPLACE(phone_number,'^/(?:011.44.)([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})$',
'\1-\2-\3') regex_output
from employees;

but it doesn't work, the next one works for the format 515.123.4567

'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})$'

but they have some phone numbers like 011.44.1345.729268 and I want to ignore 011.44 if exists at the beginning of numbers using one regex syntax if possible.

This is supposed to be the ignore syntax but its not working ^/(?:011.44.) or ^/(?!011.44.) I also tried ^/(?!([[:digit:]]{3})\.([[:digit:]]{2})\.) again doesn't work Note I can do it using other functions, this question related to regex only and I am using oracle HR database

--------------------------------------------------
|  original numbers     |      expected output   |
--------------------------------------------------
|       phone_number    |     regex_output       |
--------------------------------------------------
|   515.123.4567        |   515-123-4567         | 
|   515.123.4567        |   515-123-4567         | 
|   515.123.4567        |   515-123-4567         | 
|   515.123.4567        |   515-123-4567         | 
|   011.44.1345.729268  |   134-572-9268         | 
|   011.44.1345.729268  |   134-572-9268         |
|   011.44.1345.729268  |   134-572-9268         |
|   011.44.1345.729268  |   134-572-9268         |
--------------------------------------------------

Fiddle sample

Update:

If anyone else is wondering I found out that in oracle non capturing groups are not supported

(?:...), non-capturing groups, are not supported, you should replace them with capturing groups. (Note that (:? is not a non-capturing group, it is just an optional colon at the start of the second capturing group in the pattern). -- Wiktor Stribiżew

Improve this question
1
  • Well, I'm afraid that at the moment it's not upvoteable! You should provide a fiddle (dbfiddle.uk) with a few sample data points and your desired result! At the moment, it's very difficult to get a feeling for exactly what you want. I have no visual image of your input string(s) (please provide ~ 5) and your desired result! Commented Sep 16, 2021 at 14:35

1 Answer 1

Reset to default
0

I suggest you normalize your strings before applying the regular expression:

REGEXP_REPLACE(
  REPLACE(phone_number, '.'),
  '^(01144)?([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{4})$',
  '\2-\3-\4'
)

fiddle

Improve this answer
3
  • Thanks one for question, is ' (expression)? ' the syntax for ignore? I understand what you did but my question in this comment is, is this how I am supposed to ignore certain patterns Commented Sep 16, 2021 at 16:36
  • 1
    You should probably become familiar with the regular expression syntax before trying to use it. ? Matches zero or one occurrence. Commented Sep 16, 2021 at 22:03
  • Thanks but the best way i know how to do it, is by using non-capturing groups and i thought i had the syntax wrong, but it seems the syntax is not supported in oracle in the first place as i updated my question Commented Sep 18, 2021 at 4:30

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.