Oracle REGEXP_REPLACE help adding dashes to a number string

Question

Can someone help me write an Oracle REGEXP_REPLACE statement to add dashes to a number like this...

5310009618390
5310-00-961-8390

I'm thinking I need regexp_replace but maybe not, maybe just replace witll work. Have tried both and not gotten anywhere.

select replace(t.column, 5310009618390, '-') from table t

Have you tried SUBSTRING? SELECT SUBSTRING(t.column,0,4) + '-' + SUBSTRING(t.column,4,2) + '-' etc. — Melanie
– Melanie, Commented May 14, 2015 at 15:28
No I had not tried that Malanie. The ultimate goal is to update the database, I'm using a select right now just for testing. — Palm Bay
– Palm Bay, Commented May 14, 2015 at 15:42
The first example Oracle provides on regexp_replace is the phone number problem which is very, very close to the solution provided. docs.oracle.com/cd/B28359_01/server.111/b28286/…. — Patrick Bacon
– Patrick Bacon, Commented May 14, 2015 at 17:37
Thanks Patrick, I did see that page on my search but failed to scroll down far enough to see the examples. Will take a look at it now to see if it helps me understand Gary's solution any better. — Palm Bay
– Palm Bay, Commented May 14, 2015 at 20:09

Gary_W · Accepted Answer · 2015-05-14 16:06:08Z

4

Well first you need to be able to describe what you want to do to understand how to build the expression. For example, it appears to be: group the 1st 4 numbers, then the next 2 numbers, then the next 3 numbers then the next 4 numbers. If this is accurate:

select regexp_replace('5310009618390', '(\d{4})(\d{2})(\d{3})(\d{4})', '\1-\2-\3-\4') from dual;

This expression "remembers" the groups as described above, then displays them with the dashes in between.

It's important to be able to describe what you want to do, as that will help you to build the expression. i.e. If the rule is to group the 1st 4 characters (instead of numbers) then the expression would be different, etc.

Note this example is for REGEXP_REPLACE which can be expensive depending on the size of the dataset you will be operating on. If the rules are simple, it may be faster to just chop it up using SUBSTR() and concatenate the pieces, adding the dashes as Melanie suggests (only use || for concatenation not +).

edited May 14, 2015 at 16:06

answered May 14, 2015 at 16:00

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

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

3 Comments

Palm Bay Over a year ago

That worked perfect Gary, thank you very much. Sorry I didn't make it as clear as it should have been, but you nailed. I appreciate it.

Gary_W Over a year ago

Glad to help, just be sure you have considered all forms that the number could be in in the data as the expression depends on that. Is it the entire string? embedded in a string? Always the same length? Could multiple numbers appear in the string? etc.

Palm Bay Over a year ago

Yea, the string of numbers will always be the same length. It was really just a one time thing so fixed the problem and can now move on. Thanks again.

Collectives™ on Stack Overflow

Oracle REGEXP_REPLACE help adding dashes to a number string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related