CODEWITHSUNDEEP
oracle-databasereplaceregexp-replace
Palm Bay
Palm Bay

Reputation: 11

Oracle REGEXP_REPLACE help adding dashes to a number string

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

Upvotes: 0

Views: 2366

Answers (1)

Gary_W
Gary_W

Reputation: 10360

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 +).

Upvotes: 4

Related Questions