Splitting string with REGEXP

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Splitting string with REGEXP

Postby Art » Wed Mar 13, 2013 1:57 pm

Hi,

Breaking my head on how to split a string to lower, upper, numeric...
Here's the example of the string to split:

ArtADB1234567e9876540

The result should be as follows:

Art ADB 1234567 e 9876540.

These are separate values to be inserted in some table with the same number of columns.
Is it possible to do such thing with REGEXP please? Ideally i'd want to use SQL only.

Thank you very much.
Art
Member
 
Posts: 21
Joined: Sat Aug 27, 2011 9:24 pm

Re: Splitting string with REGEXP

Postby Tim... » Wed Mar 13, 2013 2:15 pm

Hi.

Are they always in the same order/format?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Splitting string with REGEXP

Postby Art » Wed Mar 13, 2013 2:41 pm

Tim... wrote:Hi.

Are they always in the same order/format?

Cheers

Tim...



For immed. purpose yes, always the same format and order.
Thank you.
Art
Member
 
Posts: 21
Joined: Sat Aug 27, 2011 9:24 pm

Re: Splitting string with REGEXP

Postby Tim... » Wed Mar 13, 2013 4:52 pm

Hi.

You mean like this?

Code: Select all
VARIABLE mystring VARCHAR2(50);
EXEC :mystring:='ArtADB1234567e9876540';

COLUMN col1 FORMAT A15
COLUMN col2 FORMAT A15
COLUMN col3 FORMAT A15
COLUMN col4 FORMAT A15
COLUMN col5 FORMAT A15

SELECT REGEXP_SUBSTR(:mystring, '[A-Z][a-z]+', 1, 1) col1,
       REGEXP_SUBSTR(:mystring, '[A-Z]+', 1, 2) col2,
       REGEXP_SUBSTR(:mystring, '[0-9]+', 1, 1) col3,
       REGEXP_SUBSTR(:mystring, '[a-z]+', 1, 2) col4,
       REGEXP_SUBSTR(:mystring, '[0-9]+', 1, 2) col5
FROM   dual;

COL1            COL2            COL3            COL4            COL5
--------------- --------------- --------------- --------------- ---------------
Art             ADB             1234567         e               9876540

1 row selected.

SQL>


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Splitting string with REGEXP

Postby Art » Wed Mar 13, 2013 5:16 pm

Tim... wrote:Hi.

You mean like this?

Code: Select all
VARIABLE mystring VARCHAR2(50);
EXEC :mystring:='ArtADB1234567e9876540';

COLUMN col1 FORMAT A15
COLUMN col2 FORMAT A15
COLUMN col3 FORMAT A15
COLUMN col4 FORMAT A15
COLUMN col5 FORMAT A15

SELECT REGEXP_SUBSTR(:mystring, '[A-Z][a-z]+', 1, 1) col1,
       REGEXP_SUBSTR(:mystring, '[A-Z]+', 1, 2) col2,
       REGEXP_SUBSTR(:mystring, '[0-9]+', 1, 1) col3,
       REGEXP_SUBSTR(:mystring, '[a-z]+', 1, 2) col4,
       REGEXP_SUBSTR(:mystring, '[0-9]+', 1, 2) col5
FROM   dual;

COL1            COL2            COL3            COL4            COL5
--------------- --------------- --------------- --------------- ---------------
Art             ADB             1234567         e               9876540

1 row selected.

SQL>


Cheers

Tim...




Yep, it is perfect. Thank you very much!
Art.
Art
Member
 
Posts: 21
Joined: Sat Aug 27, 2011 9:24 pm

Re: Splitting string with REGEXP

Postby Tim... » Wed Mar 13, 2013 5:41 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 4 guests