Split String by delimiter position using oracle SQL 28.9 X 30.6

I have a string which contains a '48.6 X 90.6' at COLUMN DIE_SIZE. I want to split this string into size_x and size_y. I can only find a way to split the die_y

Example Strings:

I write SQL by regexp_substr but i can substring only DIE_Y. Why is this happening and how can I fix it?

select distinct  
   DIE_SIZE,
   regexp_substr(replace(replace(DIE_SIZE,' ',''),'x','X'), '[^X]+$', 2, 1) as die_x,
   regexp_substr(replace(replace(DIE_SIZE,' ',''),'x','X'), '[^X]+$', 1, 1) as die_y        
from ORD_DIE_LOT
              

Output from SQL:

Expected output:

First you must split your string into separate rows using RECURSIVE CTE, after that you can split chunks into dimentions.

Try the below SQL:

with source_data as (
    select '48.6 X 90.6 77.9 X 57.0 47.0 X 40.0 57.0 X 71.0 61.0 X 80.0 54.5 X 74.0 42.0 X 40.0 80.0 X 85.0' str from dual
),
dimensions as (
    select regexp_substr (str, '[0-9.]+ X [0-9.]+', 1, level) value
    from source_data
    connect by level <= REGEXP_COUNT(str, 'X')
) select 
    value,
    regexp_substr (value, '[0-9.]+', 1, 1) x,
    regexp_substr (value, '[0-9.]+', 1, 2) y
from dimensions;

Here you can try and test SQL code

The result of query:

|-------------|------|-------|
| VALUE       | X    | Y     |
|-------------|------|-------|
| 48.6 X 90.6 | 48.6 |  90.6 |
| 77.9 X 57.0 | 77.9 |  57.0 |
| 47.0 X 40.0 | 47.0 |  40.0 |
| 57.0 X 71.0 | 57.0 |  71.0 |
| 61.0 X 80.0 | 61.0 |  80.0 |
| 54.5 X 74.0 | 54.5 |  74.0 |
| 42.0 X 40.0 | 42.0 |  40.0 |
| 80.0 X 85.0 | 80.0 |  85.0 |

This could be done without regexp, using just the basic string functions and Case expression ...

WITH      --  S a m p l e    D a t a :
  tbl (DIE_SIZE) AS 
    ( Select '48.6 X 90.6 77.9 X 57.0 47.0 X 40.0 57.0 X 71.0 61.0 X 80.0 54.5 X 74.0 42.0 X 40.0 80.0 X 85.0' "DIE_SIZE"
      From Dual
    ),

... cte generating number of rows as number of dimensions in the string and using Replace() formats delimiters for string to be splitted on in main sql ...

  dims ( RN, DIE_SIZE, DIE_SIZE_REMAIN ) AS
    ( Select    LEVEL "RN",
                Replace(Replace(DIE_SIZE || '/', ' X ', 'X'), ' ', '/' ) "DIE_SIZE",
           Case When Level = 1 Then Replace(Replace(DIE_SIZE || '/', ' X ', 'X'), ' ', '/' ) 
           Else SubStr( Replace(Replace(DIE_SIZE || '/', ' X ', 'X'), ' ', '/' ),
                        InStr(Replace(Replace(DIE_SIZE || '/', ' X ', 'X'), ' ', '/' ), '/', 1, LEVEL - 1) + 1
                      ) 
           End "DIE_SIZE_REMAIN"
      From      tbl
      Connect By LEVEL <= Length(DIE_SIZE) - Length(Replace(DIE_SIZE, 'X', '')) 
    )

... getting final result by fetching dimension pair chunks 1 by 1, then splitting each into X and Y dimension...

--    M a i n   S Q L : 
Select    Replace(SubStr(DIE_SIZE_REMAIN, 1, InStr(DIE_SIZE_REMAIN, '/') - 1), 'X', ' X ') "DIE_SIZE",
          SubStr( SubStr(DIE_SIZE_REMAIN, 1, InStr(DIE_SIZE_REMAIN, '/') - 1),
                  1, 
                  InStr(SubStr(DIE_SIZE_REMAIN, 1, InStr(DIE_SIZE_REMAIN, '/') - 1), 'X') - 1
                ) "DIM_X",
          SubStr( SubStr(DIE_SIZE_REMAIN, 1, InStr(DIE_SIZE_REMAIN, '/') - 1),
                  InStr(SubStr(DIE_SIZE_REMAIN, 1, InStr(DIE_SIZE_REMAIN, '/') - 1), 'X') + 1
                ) "DIM_Y"
From      dims

/*    R e s u l t :
DIE_SIZE     DIM_X   DIM_Y
-----------  -----   -----
48.6 X 90.6   48.6    90.6
77.9 X 57.0   77.9    57.0
47.0 X 40.0   47.0    40.0
57.0 X 71.0   57.0    71.0
61.0 X 80.0   61.0    80.0
54.5 X 74.0   54.5    74.0
42.0 X 40.0   42.0    40.0
80.0 X 85.0   80.0    85.0    */

See the fiddle here.

This method modifies Slava's answer to eliminate one of the CTE's. Uses the "i" argument for case-insensitive matching on the "X", and remembered groups to select the part of the string you want to return as X and Y. Note if there are multiple rows of DIE_SIZE strings this would need to be modified further.

with source_data(str) as (
    select 'DIE_SIZE 48.6 X 90.6 77.9 x 57.0 47.0 X 40.0 57.0 X 71.0 61.0 x 80.0 54.5 X 74.0 42.0 X 40.0 80.0 X 85.0' 
    from dual
)
select   
  regexp_substr (str, '[0-9.]+ X [0-9.]+',   1, level, 'i')  DIE_SIZE,
  regexp_substr (str, '([0-9.]+) X [0-9.]+', 1, level, 'i', 1) DIE_X,
  regexp_substr (str, '[0-9.]+ X ([0-9.]+)', 1, level, 'i', 1) DIE_Y
from source_data
  connect by level <= REGEXP_COUNT(str, 'X', 1, 'i');


DIE_SIZE        DIE_X      DIE_Y     
--------------- ---------- ----------
48.6 X 90.6     48.6       90.6      
77.9 x 57.0     77.9       57.0      
47.0 X 40.0     47.0       40.0      
57.0 X 71.0     57.0       71.0      
61.0 x 80.0     61.0       80.0      
54.5 X 74.0     54.5       74.0      
42.0 X 40.0     42.0       40.0      
80.0 X 85.0     80.0       85.0      

8 rows selected.

Due to bad formatting in the request, it looked like you had a large string with many entries, while you really have separate rows per entry. This is why you got answers suggesting a recursive approach.

As is, you only need a simple query, like you already wrote yourself. But as for the regular expressions you made things more complicated than need be.

You want numbers consisting of digits and a point, which is '[0-9.]+'. Of these you want the first and the second.

select 
 die_size,
 regexp_substr(die_size, '[0-9.]+', 1, 1) as die_x,
 regexp_substr(die_size, '[0-9.]+', 1, 2) as die_y
from ord_die_lot;