This forum is currently locked. You can't register or post questions at this time. (read more)

Splitting string with REGEXP

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Art
Member
Posts: 21
Joined: Sat Aug 27, 2011 9:24 pm

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.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

Art
Member
Posts: 21
Joined: Sat Aug 27, 2011 9:24 pm

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.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

Art
Member
Posts: 21
Joined: Sat Aug 27, 2011 9:24 pm

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.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 1 guest

cron