Splitting string with REGEXP

[Art]

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...]

Hi.

Are they always in the same order/format?

Cheers

Tim...

[Art]

Hi.

Are they always in the same order/format?

Cheers

Tim...

For immed. purpose yes, always the same format and order.
Thank you.

[Tim...]

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...

[Art]

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...]