regex - Regular expressions, what a trouble! -

i need kind resolve question.

i state not able use regolar expressions oracle pl/sql, promise i'll study them asap!

please suppose have table column called my_column of type varchar2(4000).

this colums populated follows:

description of first no.;00123457;description of 2nd number;91399399119;third descr.;13456

you can see strings composed couple of numbers (which may begin zero), , strings (containing alphanumeric characters, , dot, ', /, \, , on):

description1;number1;description2;number2;description3;number3;......;descriptionn;numbern

of course, n not known, means number of couples every record can vary record record.

in every couple first element number (which may begin zero, repeat), , second element string.

the field separator semicolon (;).

i transform numbers follows:

00123457 ===> 001-23457 91399399119 ===> 913-99399119 13456 ===> 134-56

this means, after first 3 digits of number, need put dash "-"

how can achieve using regular expressions?

thank in advance kind cooperation!

i don't know oracle/pl/sql, can provide regex:

([[:digit:]]{3})([[:digit:]]+)

matches number of @ least 4 digits , remembers first 3 separately rest.

regexbuddy constructs following code snippet this:

declare     result varchar2(255); begin     result := regexp_replace(subject, '([[:digit:]]{3})([[:digit:]]+)', '\1-\2', 1, 0, 'c'); end;

if need make sure numbers directly surrounded ;, can alter slightly:

(^|;)([[:digit:]]{3})([[:digit:]]+)(;|$)

however, not work if 2 numbers can directly follow each other (12345;67890 match first number). if that's not problem, use

result := regexp_replace(subject, '(^|;)([[:digit:]]{3})([[:digit:]]+)(;|$)', '\1\2-\3\4', 1, 0, 'c');

shell