I need filter the phone Records from table with following requirmnet?

by Rakesh
(Indai)

I/P : Phone column data are like


Phone
------
77---78-584--12
77---7-8584--12
77-45---78584--12
77----78584--12-545

O/P:

77-78-584-12
77-7-8584-12
77-45-78584-12
77-78584-12-545


I know the solution with REGEXP_Replace is there any other logic for resolve the same?

Comments for I need filter the phone Records from table with following requirmnet?

Average Rating starstarstarstarstar

Click here to add your own comments

Sep 19, 2016
Rating
starstarstarstarstar
Just go for the simplest solution
by: Anonymous

The REGEXP_REPLACE function in Oracle SQL would seem to be the ideal (and only pratical) solution to your problem. This function takes in a character string, a regualr expression (i.e. a pattern including meta characters) against which the input string is compared and (optionally) a string to replace the matches found.

In this case tehrefore, the following SQL statement would replace multiple occurrences of '-' with a single occurrence.

select regexp_replace(phone_number,'--*','-') from input_data;


For example, let's take the first number which is 77---78-584--12 and use that in the statement.

select regexp_replace(77---78-584--12,'--*','-') from dual;


The result is:
77-78-584-12
.

Obviously the function only works with what you give it, so the numbers won't be formatted in the same way. To do that yo would probbaly be better off by stripping out all the '-' characters and then formatting the result how you want it.

As for alternatives - well you could write your own function in PL/SQL using a combination of INSTR and REPLACE but why bother when Oracle have already created something that will do the job for you?

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.