# Regular expressions, what a trouble!

I need your kind help to resolve this question.

I state that I am not able to use regolar expressions with Oracle PL/SQL, but I promise that I'll study them ASAP!

Please suppose you have a table with a column called MY_COLUMN of type VARCHAR2(4000).

This colums is populated as follows:

Description of first no.;00123457;Description of 2nd number;91399399119;Third Descr.;13456

You can see that the strings are composed by couple of numbers (which may begin with zero), and strings (containing all alphanumeric characters, and also dot, ', /, \, and so on):

Description1;Number1;Description2;Number2;Description3;Number3;......;DescriptionN;NumberN

Of course, N is not known, this means that the number of couples for every record can vary from record to record.

In every couple the first element is always the number (**which may begin with zero**, I repeat), and the second element is the string.

The field separator is ALWAYS semicolon (;).

I would like to transform the numbers as follows:

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

This means, after the first three digits of the number, I need to put a dash "-"

How can I achieve this using regular expressions?

Thank you in advance for your kind cooperation!

## Answers

I don't know Oracle/PL/SQL, but I can provide a regex:

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

matches a number of at least four digits and remembers the first three separately from the rest.

RegexBuddy constructs the following code snippet from this:

DECLARE result VARCHAR2(255); BEGIN result := REGEXP_REPLACE(subject, '([[:digit:]]{3})([[:digit:]]+)', '\1-\2', 1, 0, 'c'); END;

If you need to make sure that those numbers are always directly surrounded by ;, you can alter this slightly:

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

However, this will not work if two numbers can directly follow each other (12345;67890 will only match the first number). If that's not a problem, use

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