En ciertas ocasiones nos vemos en la necesidad de almacenar una serie de valores distintos en una misma columna. Especialmente cuando estamos tratando con datos de configuración que pueden variar en el número y valores posibles con el tiempo. Explotar esta información desde SQL suele presentar muchos inconvenientes puesto que se trata de información sin normalizar y que requiere tratar con cadenas de texto de alta variabilidad.

Gracias a la implantación de expresiones regulares dentro de SQL podemos mejorar las alternativas a la hora de consultar este tipo de datos.

Tomemos, a modo de prueba, las siguientes cadenas de texto:

TYPE=String#LENGTH=18#DEFAULT=empty
LENGTH=1#TYPE=Number#DECIMALS=0#DEFAULT=0
LENGTH=10#DEFAULT=1.00#TYPE=Number#DECIMALS=2

Mediante el uso de las funciones “regexp_instr” y “regexp_substr” dentro de una consulta SELECT podemos extraer los distintos componentes de cada una de las cadenas y, por ejemplo, mostrarlos como columnas:

with test_tab as (
    select 1 cfg, 'TYPE=String#LENGTH=18#DEFAULT=empty' config from dual
    union all
    select 2 cfg, 'LENGTH=1#TYPE=Number#DECIMALS=0#DEFAULT=0' config from dual
    union all
    select 3 cfg, 'LENGTH=10#DEFAULT=1.00#TYPE=Number#DECIMALS=2' config from dual)
select cfg,
       decode((regexp_instr(config, '(^|#)TYPE=(.+)($|#)')),     0, '', (regexp_substr(config, '[^#]+', (regexp_instr(config, '(^|#)TYPE=(.+)($|#)')),     1))) cfg_TYPE,
       decode((regexp_instr(config, '(^|#)LENGTH=(.+)($|#)')),   0, '', (regexp_substr(config, '[^#]+', (regexp_instr(config, '(^|#)LENGTH=(.+)($|#)')),   1))) cfg_LENGTH,
       decode((regexp_instr(config, '(^|#)DECIMALS=(.+)($|#)')), 0, '', (regexp_substr(config, '[^#]+', (regexp_instr(config, '(^|#)DECIMALS=(.+)($|#)')), 1))) cfg_DECIMALS,
       decode((regexp_instr(config, '(^|#)DEFAULT=(.+)($|#)')),  0, '', (regexp_substr(config, '[^#]+', (regexp_instr(config, '(^|#)DEFAULT=(.+)($|#)')),  1))) cfg_DEFAULT,
       config
from test_tab;

Resultado:

CFG	CFG_TYPE	CFG_LENGTH	CFG_DECIMALS	CFG_DEFAULT	CONFIG
---	-----------	----------	-------------	-----------	----------------------------------------
1	TYPE=String	LENGTH=18	DEFAULT=empty			TYPE=String#LENGTH=18#DEFAULT=empty
2	TYPE=Number	LENGTH=1	DECIMALS=0	DEFAULT=0	LENGTH=1#TYPE=Number#DECIMALS=0#DEFAULT=0
3	TYPE=Number	LENGTH=10	DECIMALS=2	DEFAULT=1.00	LENGTH=10#DEFAULT=1.00#TYPE=Number#DECIMALS=2

Estas mismas funciones nos pueden servir para filtrar los datos en la cláusula WHERE o para modificar los datos de la cadena a través del operador SET en una operación UPDATE.

Por último, mediante la cláusula CONNECT BY, podemos listar todos los datos de una de las listas y mostrarlos como filas distintas en el resultado de la consulta:

with test_tab as (select 1 cfg, 'TYPE=String#LENGTH=18#DEFAULT=empty' config from dual)
select cfg, config, regexp_substr(config, '[^#]+', 1, level) key_value
from test_tab
connect by regexp_substr(config, '[^#]+', 1, level) is not null;

Resultado:

CFG	CONFIG					KEY_VALUE
---	-----------------------------------	-------------
1	TYPE=String#LENGTH=18#DEFAULT=empty	TYPE=String
1	TYPE=String#LENGTH=18#DEFAULT=empty	LENGTH=18
1	TYPE=String#LENGTH=18#DEFAULT=empty	DEFAULT=empty