Oracle regexp_replace: Don't replace the first character, replace others

Question

I am writing a sql, and I am using regexp_replace function for this.

My aim is to replace the characters like '|', '\' etc with '-'.

The problem which I am facing is that it replaces the '+', which is at the beginning.

For eg:

The phone number is: +49 |0| 941 78878544

I have to repalce the '|' with '-'.

My code is this:SELECT regexp_replace(phone,'\D','-') FROM PHONE_TBL WHERE EMPLID = employee;

I get the output as: -49--0--941-78878544

This code replaces the space , along with the '+' in the beginning.

I want the '+' to remain, if it is there in the beginning, and if phone numbers have spaces among them, that also should remain.

For '+', I have figured out i should match the beginning of the string, then,must check for non numeric digit, and then escape, but not able to code.

And for space in between, similar approach.

Any help in this, thanks.

Hawk · Accepted Answer · 2013-12-04 09:54:27Z

1

If you want to replace all instances of '\' and '|' with '-', use the following:

SELECT REGEXP_REPLACE(phone,'[\|\]','-') 
FROM phone_tbl 
WHERE emplid = employee;

The square brackets define a set of characters to match. The '\|' means match a pipe character; the '\' means match a backslash character. If you want to replace more characters other than backslash and pipe then you add them between the square brackets.

edited Dec 4, 2013 at 9:54

Hawk

5,18014 gold badges55 silver badges78 bronze badges

answered Dec 4, 2013 at 9:32

user1578653

5,03817 gold badges48 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vamosrafa Over a year ago

Means, to repalce '/', i shall this: REGEXP_REPLACE(phone,'[\|/]','-')

Collectives™ on Stack Overflow

Oracle regexp_replace: Don't replace the first character, replace others

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related