Archivo de la categoría: SQL

De fecha a epoch y viceversa

Deja un comentario

Estas consultas están tomadas del siguiente artículo de Chris Saxon en el que explica con más detalle los detalles y excepciones a tener en cuenta:

How to convert UNIX epochs to datetime values in Oracle Database

Las consultas utilizadas son las siguientes:

-- Epoch to time: datetime and timestamp
select to_date('01/01/1970','dd/mm/yyyy') + (:epoch / 86400) epoc_dt,
       to_timestamp('01/01/1970 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + numtodsinterval (:epoch, 'second') epoc_ts
from dual;

-- Time to epoch: datetime
select round((to_date(:datetime, 'dd/mm/yyyy hh24:mi:ss') - to_date('01/01/1970','dd/mm/yyyy')) * 86400) dt_epoch
from dual;

-- Time to epoch: timestamp
select   extract(day from epoch_dsi) * 86400 
       + extract(hour from epoch_dsi) * 3600
       + extract(minute from epoch_dsi) * 60
       /* remove fractional seconds */
       + trunc(extract(second from epoch_dsi)) ts_epoch
from (select to_timestamp(:datetime, 'dd/mm/yyyy hh24:mi:ss') - to_timestamp('01/01/1970 00:00:00', 'dd/mm/yyyy hh24:mi:ss') epoch_dsi
    from dual);

Enmascarado de teléfonos

Deja un comentario

Una expresión regular para enmascarar teléfonos (con prefijo internacional).

SELECT result1 ORIGINAL_PHONE,
    regexp_replace(
        regexp_replace(result1,'[^[:digit:]+]',''), -- Remove non-digit and special phone characters
        '^(00[[:digit:]]{2}|\+[[:digit:]]{2})([[:alnum:]]*)([[:alnum:]]{3})$', -- Phone pattern
        '\1*\3') MASK_PHONE -- Mask
FROM ( 
    WITH test AS
     (SELECT '0034910000000,+34910000000' col1 FROM dual) 
    SELECT regexp_substr(col1, '[^,]+', 1, rownum) result1 
      FROM test 
    CONNECT BY LEVEL <= regexp_count(col1, ',') + 1);

El resultado es:

ORIGINAL_PHONE   MASK_PHONE
---------------- ----------------
0034910000000    0034*000
+34910000000     +34*000

Enmascarado de correos electrónicos

Deja un comentario

Una expresión regular para enmascarar correos electrónicos.

SELECT result1 ORIGINAL_EMAIL,
    regexp_replace(
        regexp_replace(result1, '[^[:alnum:]._%+-@]', ''), -- Remove non-digit, non-alphabetic and special email characters
        '^([[:alnum:]._%+-]{2})([[:alnum:]._%+-]*)(@)([[:alnum:]._%+-]{2})([[:alnum:]._%+-]*)(\.{1}[[:alpha:]]{2,}$)', -- Email pattern
        '\1*\3\4*\6') MASK_EMAIL -- Mask
FROM ( 
    WITH test AS
     (SELECT 'n.surname@domain.ext,nsurname@subd.domain.ext,name-surname@domain.ext' col1 FROM dual) 
    SELECT regexp_substr(col1, '[^,]+', 1, rownum) result1 
      FROM test 
    CONNECT BY LEVEL <= regexp_count(col1, ',') + 1);

El resultado es:

ORIGINAL_EMAIL                   MASK_EMAIL
-------------------------------- -------------
n.surname@domain.ext             n.*@do*.ext
nsurname@subd.domain.ext         ns*@su*.ext
name-surname@domain.ext          na*@do*.ext

Select sobre un JSON con una lista

Deja un comentario

Select para extraer de un JSON los elementos un nodo de tipo lista.

select id
from json_table('{"list_ids": [1,2,3,4]}'
  format json, '$.list_ids[*]'
columns (
  id number path '$'));

        ID
----------
         1
         2
         3
         4

Select sobre un JSON con varios items

Deja un comentario

Select para extraer datos de un JSON que informa varios items.

select id, nombre, familia
from json_table('[{id: 1, name: "Naranja", family: "Cítrico"},
                  {id: 2, name: "Tomate",  family: "Hortaliza"}]'
  format json, '$[*]'
columns (
  id      number   path '$.id',
  nombre  varchar2 path '$.name',
  familia varchar2 path '$.family'));

ID    NOMBRE          FAMILIA
----- --------------- ---------------
1     Naranja         Cítrico
2     Tomate          Hortaliza

Conversión de fechas RFC3339 a Oracle timestamp

Deja un comentario

La consulta siguiente muestra la conversión del formato de fechas RFC3339, muy común en servicios REST y objetos JSON, al timpo de dato timestamp nativo en Oracle.

select TO_TIMESTAMP_TZ('2020-12-15T15:20:28Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF9 TZH:TZM') as RFC3339_to_timestamp_zulu,
       TO_TIMESTAMP_TZ('2020-12-15T15:20:28Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF9 TZH:TZM') AT TIME ZONE 'Europe/Madrid' as RFC3339_to_timestamp_madrid
from dual;

RFC3339_TO_TIMESTAMP_ZULU         RFC3339_TO_TIMESTAMP_MADRID                
--------------------------------- -------------------------------------------
15/12/2020 15:20:28,000000000 GMT 15/12/2020 16:20:28,000000000 EUROPE/MADRID

Tratamiento de NIF españoles (parte 2)

Deja un comentario

Una expresión regular para tratar los NIF españoles eliminando todos los caracteres que no sean números o letras. Además, comprueba el tamaño del NIF para eliminar el código de país (identificador fiscal comunitario) si fuera necesario:

select result1 ORIGINAL_TAXID, 
    upper(
        regexp_replace( 
            regexp_replace(result1, '[^[:alnum:]]', ''), -- Remove non-digit and non-alphabetic characters 
            '^(ES)([[:alnum:]]{9})$', -- Identify spanish NIFs with country code 
            '\2')) CLEAN_AND_NATIONAL_TAXID -- Remove spanish country code 
from ( 
    WITH test AS
     (SELECT 'ESA45678901,B23456789,GB3456789,12345678L,B234567890123,ES-A45678901,B.23456789,GB3::456789,B234_567_890123' col1 FROM dual) 
    SELECT regexp_substr(col1, '[^,]+', 1, rownum) result1 
      FROM test 
    CONNECT BY LEVEL <= regexp_count(col1, ',') + 1);

El resultado es:

ORIGINAL_TAXID	CLEAN_AND_NATIONAL_TAXID
--------------  ------------------------
ESA45678901	A45678901
B23456789	B23456789
GB3456789	GB3456789
12345678L	12345678L
B234567890123	B234567890123
ES-A45678901	A45678901
B.23456789	B23456789
GB3::456789	GB3456789
B234_567_890123	B234567890123

En el artículo Tratamiento de NIF españoles se muestra una expresión regular para añadir el código de país.

Extraer valores de una lista contenida en una columna

Deja un comentario

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

Select sobre colecciones PL/SQL correlacionadas

Deja un comentario

Oracle permite acceder a los valores de colecciones PL/SQL desde sentencias SQL. Esta propiedad del comando SELECT se llama table_collection_expression y muestra el contenido de una colección como si de una tabla se tratara.

Para que el proceso funcione correctamente es necesario que la colección esté declarada en el esquema, no dentro de un paquete:

-- Create PL/SQL collection
create or replace type ty_number is table of number;

-- Select from one PL/SQL collection
select * from table(ty_number(10,20,30,50,40));

La sentencia daría como resultado:

COLUMN_VALUE
------------
10
20
30
50
40

Esta propiedad puede resultarnos muy útil cuando estamos programando en PL/SQL y, por ejemplo, necesitamos extraer datos de una consulta a través de un proceso bulk collection y compararlos con registros de otras tablas.

Pero, ¿qué ocurre si necesitamos cruzar datos de dos colecciones PL/SQL y datos de tablas? ¿Cómo vamos a correlacionar los elementos de varias colecciones entre sí? En estos casos podemos recurrir a una sentencia join en combinación con la cláusula connect by level para generar los índices que nos permitirán recorrer las colecciones:

-- Select from two correlated PL/SQL collections
select coll_index, coll_1_value, coll_2_value
from (select level coll_index from dual connect by level <= 5) gen_idx -- Generate index for iterate collections
     join (select rownum rn, column_value coll_1_value from table(ty_number(10,20,30,50,40))) coll_1 on gen_idx.coll_index = coll_1.rn -- Collection 1
     join (select rownum rn, column_value coll_2_value from table(ty_number(11,21,31,51,41))) coll_2 on gen_idx.coll_index = coll_2.rn; -- Collection 2

La ejecución de la sentencia produce el siguiente resultado:

COLL_INDEX	COLL_1_VALUE	COLL_2_VALUE
----------	------------	------------
1		10		11
2		20		21
3		30		31
4		50		51
5		40		41

Como puede apreciarse al fijarnos en las filas 4 y 5, los elementos de las colecciones mantienen el orden original y la correlación entre sus elementos.