Как вычленить нужную информацию с переменной длиной из сплошной строки SQL

Предложу вот такой вариант поиска нужных данных в строке по заданному ключу

CREATE OR REPLACE FUNCTION GETDATA(
  FKEY VARCHAR2,
  FSTRING VARCHAR2
) return varchar2
IS
  pos number;
  poslen number;
  searchlen number;
  result varchar2;
BEGIN
  -- Получаем индекс начало подстроки заданного ключа
  select INSTR(FSTRING, FKEY) INTO pos from dual; 
  -- Получаем длину ключа
  select LENGHT(FKEY) INTO poslen from DUAL;
  -- Получаем длину нужной нам строки
  select to_number(substr(FSTRING, pos + poslen, 3)) into searchlen from dual;
  -- Выдергиваем нужную нам строку
  select substr(FSTRING, pos + poslen + 3, searchlen) into result from dual;
  -- Возвращаем результат
  return result;
END;
-- ====== Делаем выборку ======
select 
    GETDATA('XWID', t.field) XWID
  , GETDATA('XDEV', t.field) XDEV
  , GETDATA('XZON', t.field) XZON
  , GETDATA('XPHN', t.field) XPHN
from mySomeTable t;

Для того, что бы функцию можно было использовать в select запросах, она должна быть создана в схеме(Не в процедуре).

P.S. Так как проверить на текущий момент негде, могут быть ошибки, надо подтюнить.
Однако, как вариант решения, должно подойти

Если вы используете Oracle 18c или более позднюю версию.

Ваши данные очень похожи на JSON. Итак, будем составлять JSON.

После этого все легко.

Последовательный порядок токенов не имеет значения.

Если вам нужны значения, начиная с 4-й позиции, вы можете использовать для этого функцию SUBSTR(json_value(j, '$.XWID'), 4) и так далее.

SQL Fiddle

SQL

CREATE TABLE tbl (ID int, fld_126 VARCHAR2(100) NOT NULL);
 
INSERT INTO tbl (ID, fld_126) 
WITH rs AS ( 
    SELECT 1, 'XWID9998887766XDEVmmmXZON8XPHN9998887766'      FROM dual UNION ALL 
    SELECT 2, 'XDEVppppXWID999988877766XZON4XPHN9097976655'   FROM dual 
)
SELECT * FROM rs;

SELECT ID, j
    , json_value(j, '$.XWID') as XWID -- SUBSTR(json_value(j, '$.XWID'), 4)
    , json_value(j, '$.XDEV') as XDEV
    , json_value(j, '$.XZON') as XZON
    , json_value(j, '$.XPHN') as XDEV
FROM tbl t
CROSS APPLY (
   SELECT treat(REPLACE('{' || REPLACE(REPLACE(REPLACE(REPLACE(fld_126
        ,'XWID','","XWID":"')
        ,'XDEV','","XDEV":"')
        ,'XZON','","XZON":"')
        ,'XPHN','","XPHN":"')
        , '{",','{') || '"}' as json)  As j FROM dual
) CrossApplied;

Результат

| ID | J                                                                    | XWID         | XDEV | XZON | XPHN       |
|----|----------------------------------------------------------------------|--------------|------|------|------------|
| 1  | {"XWID":"9998887766","XDEV":"mmm","XZON":"8","XPHN":"9998887766"}    | 9998887766   | mmm  | 8    | 9998887766 |
| 2  | {"XDEV":"pppp","XWID":"999988877766","XZON":"4","XPHN":"9097976655"} | 999988877766 | pppp | 4    | 9097976655 |