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');

Need Your Help

Quickly create a wrappers for classes to count undisposed instances?

c# .net testing

Is there any tool or library that will let me easily wrap a class, say DbDataReader with another class called, say WDbDataReader, for debugging purposes?

Python: Waiting for the enter key OR button press (Text Based Adventure with GUI)

python input tkinter

Hi I am making a text based adventure in python and am having trouble with taking the user input, the text the user reads is in one text box and the input goes in an entry box.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.