Validando números escritos en distintos formatos

Deja un comentario

Tenemos el siguiente problema: en una tabla hay una columna donde se  guardan números de teléfono, los cuales pueden estar repetidos y queremos que haya una sola fila por cada número de teléfono. Pero el problema es que esos números de teléfono pueden estar escritos de varias maneras, por ejemplo:

  • 11-555-9090
  • (11)555-9090
  • 115559090

 

validar01

Captura 1. Si haces clic en la imagen la verás más grande

En la Captura 1. vemos el Identificador y el Número de Teléfono que existen en siete filas de una tabla. Solamente el Número de Teléfono de la fila que tiene Identificador igual a 4 no está repetido. En todas las demás filas hay simplemente variaciones de los mismos números, escritos con diferentes formatos.

Entonces, ¿cómo podemos saber cuales filas tienen números de teléfono repetidos?

Primero, buscaremos cuales son los números repetidos, estén escritos en el formato que sea.

Para ellos usaremos la función REPLACE() la cual nos permite reemplazar una carácter (o varios caracteres) por otro carácter (o por varios caracteres). En nuestro caso reemplazaremos por un carácter vacío.

Listado 1.

SELECT
   REPLACE(ALU_TELEFO, '(', '')
FROM
   ALUMNOS

Al ejecutar el Listado 1. veremos algo como:

validar02

Captura 2. Si haces clic en la imagen la verás más grande

Si observas la Captura 2. notarás que han desaparecido los paréntesis izquierdos. Seguimos refinando nuestro SELECT.

Listado 2.

SELECT
   REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', '')
FROM
   ALUMNOS

validar03

Captura 3. Si haces clic en la imagen la verás más grande

Si observas la Captura 3. notarás que han desaparecido los paréntesis izquierdos y también los paréntesis derechos. Seguimos refinando nuestro SELECT.

Listado 3.

SELECT
   REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '')
FROM
   ALUMNOS

validar04

Captura 4. Si haces clic en la imagen la verás más grande

Como ya habrás visto, en la Captura 4. no hay ni paréntesis ni guiones. Así que buscar duplicados ahora ya es más fácil.

Listado 4.

SELECT
   REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '')
FROM
   ALUMNOS
GROUP BY
   REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '')
HAVING
   COUNT(*) >= 2

El SELECT del Listado 4. también podríamos haberlo escrito de forma más simplificada así:

Listado 5.

SELECT
   REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '')
FROM
   ALUMNOS
GROUP BY
   1
HAVING
   COUNT(*) >= 2

En ambos casos obtendremos el mismo resultado:

validar05

Captura 5. Si haces clic en la imagen la verás más grande

Donde podemos ver los números que se repiten. Ahora bien, ya sabemos cuales son los números que se repiten, pero ¿en cuáles filas están? Esa información la obtendremos mediante el siguiente SELECT

Listado 6.

SELECT
   ALU_IDENTI,
   ALU_TELEFO
FROM
   ALUMNOS
WHERE
   REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '') IN
      (SELECT
         REPLACE(REPLACE(REPLACE(ALU_TELEFO, '(', ''), ')', ''), '-', '')
       FROM
          ALUMNOS
       GROUP BY
          1
       HAVING
          COUNT(*) >= 2)

validar06

Captura 6. Si haces clic en la imagen la verás más grande

Donde la única fila que no aparece es la que tiene Identificador igual a 4, porque esa fila no tiene un Número de Teléfono que esté repetido.

Como seguramente te habrás dado cuenta mirando los SELECTs anteriores, usamos una función REPLACE() por cada carácter que deseamos extraer. Un carácter, una función REPLACE(); dos caracteres, dos funciones REPLACE(); tres caracteres, tres funciones REPLACE().

Supongamos ahora que queremos dejar en nuestra tabla solamente la primera fila de cada número. O sea que quisiéramos obtener esto:

validar07

Captura 7. Si haces clic en la imagen la verás más grande

¿Cómo lo podemos conseguir?

Listado 7.

DELETE FROM
   ALUMNOS T1
WHERE
   NOT EXISTS (SELECT
                  T2.ALU_IDENTI
               FROM
                  ALUMNOS T2
               WHERE
                  REPLACE(REPLACE(REPLACE(T1.ALU_TELEFO, '(', ''), ')', ''), '-', '') = REPLACE(REPLACE(REPLACE(T2.ALU_TELEFO, '(', ''), ')', ''), '-', '') AND
                  T1.ALU_IDENTI > T2.ALU_IDENTI)

Conclusión:

Hay varias técnicas que podemos usar para conocer si hay números duplicados aunque estén escritos con diferentes formatos, y para borrarlos en caso de necesidad. En este artículo se mostró una de esas técnicas, la cual al autor le parece mucho más sencilla que, por ejemplo, escribir un stored procedure que realice la misma tarea.

Artículos relacionados:

La función REPLACE()

Agrupando por una columna … y viendo todas las demás columnas

Obteniendo la primera fila de cada grupo

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Hallando la última palabra

2 comentarios

Ya vimos como hallar la primera palabra de un texto, ¿pero y si necesitamos hallar la última palabra, cómo lo conseguimos?

LEFT1

Captura 1. Si haces clic en la imagen la verás más grande

Listado 1.

SELECT
   RIGHT(ALU_NOMBRE, POSITION(' ' IN REVERSE (ALU_NOMBRE) || ' '))
FROM
   ALUMNOS

RIGHT1

Captura 2. Si haces clic en la imagen la verás más grande

Explicación:

La función POSITION() devuelve la posición en la cual se encuentra un string dentro de otro string. La función REVERSE() lo muestra de atrás para adelante. Si hay al menos un espacio en blanco entonces la función RIGHT() en el Listado 1. nos devolverá la última palabra. ¿Pero y si no hay ese espacio en blanco? Entonces devolverá un string vacío. Para evitarlo, se concatena un espacio en blanco y así se soluciona.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Hallando la primera palabra

Deja un comentario

En ocasiones necesitamos hallar la primera palabra de un texto, ¿cómo lo conseguimos?

LEFT1

Captura 1. Si haces clic en la imagen la verás más grande

Listado 1.

SELECT
   LEFT(ALU_NOMBRE, POSITION(' ', ALU_NOMBRE || ' '))
FROM
   ALUMNOS

LEFT2Captura 2. Si haces clic en la imagen la verás más grande

Explicación:

La función POSITION() devuelve la posición en la cual se encuentra un string dentro de otro string. Si hay al menos un espacio en blanco entonces la función LEFT() en el Listado 1. nos devolverá la primera palabra. ¿Pero y si no hay ese espacio en blanco? Entonces devolverá un string vacío. Para evitarlo, se concatena un espacio en blanco y así se soluciona.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Precaución al usar la función SUM()

3 comentarios

A veces puede parecer que se obtendrá el mismo resultado escribiendo un SELECT que tiene una función SUM() de una forma o de otra, pero lo que se obtiene no es lo esperado. Veamos un ejemplo:

SUMA1

Captura 1. Si haces clic en la imagen la verás más grande

Listado 1.

SELECT
   SUM(MOV_PRECIO) / SUM(MOV_CANTID) AS RESULTADO
FROM
   MOVIMDET

SUMA2

Captura 2. Si haces clic en la imagen la verás más grande

Listado 2.

SELECT
   SUM(MOV_PRECIO / MOV_CANTID) AS RESULTADO
FROM
   MOVIMDET

SUMA3

Captura 3. Si haces clic en la imagen la verás más grande

¿Por qué no se obtiene el mismo resultado? ¿Acaso ambos resultados no deberían ser idénticos?

Pues no, en el Listado 1. se le pide que sume todos los valores de la columna MOV_PRECIO y los divida por la suma de todos los valores de la columna MOV_CANTID

Suma de MOV_PRECIO = 200

Suma de MOV_CANTID = 2

Al dividir ambas sumas el resultado es 100

Sin embargo, en el Listado 2. para cada fila primero se halla el resultado de la división y luego se realiza la suma.

100 / 1 = 100

100 / 1 = 100

Al sumar ambos resultados se obtiene 200

El motivo por el cual se obtienen resultados diferentes es que en el Listado 1. se suman todas las filas de una sola vez, en cambio en el Listado 2. se suman de una en una.

Cuidado con esto, si no entiendes bien este concepto podrías estar obteniendo resultados incorrectos.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Restando fechas que se encuentran en distintas filas (2)

Deja un comentario

Ya habíamos visto como conseguir restar fechas que se encuentran en distintas filas:

Restando fechas que se encuentran en distintas filas

Ahora en este artículo veremos otro método para conseguir el mismo resultado. Para ello usaremos tablas autoreferenciadas.

Una tabla autoreferenciada es una tabla que tiene un JOIN a sí misma, y es algo que puede ser muy útil a veces.

Listado 1.


CREATE GENERATOR
   GEN_NUMERAR;

UPDATE
   PRUEBA1
SET
   PRU_NUMREG = GEN_ID(GEN_NUMERAR, 1);

DROP GENERATOR
   GEN_NUMERAR;

SELECT
   T1.PRU_NUMREG,
   T1.PRU_FECINI,
   T2.PRU_FECFIN,
   T2.PRU_FECFIN - T1.PRU_FECINI AS DIFERENCIA
FROM
   PRUEBA1 T1
LEFT JOIN
   PRUEBA1 T2
      ON T1.PRU_NUMREG = T2.PRU_NUMREG - 1

Como puedes ver en el Listado 1., la primera parte es igual a la que habíamos escrito en el otro artículo, lo único que cambia es el SELECT.

Usamos un LEFT JOIN para que siempre se muestre la Fecha Inicial, haya o no haya una Fecha Final correspondiente. Podrías usar solamente JOIN si únicamente te interesan las filas que tienen una Fecha Inicial y su correspondiente Fecha Final.

Nuestra tabla PRUEBA1 tiene estas filas:

FECHA02

Captura 1.

Y después de ejecutar el Listado 1. obtendremos lo que vemos en la Captura 2.

FECHA01

Captura 2.

Artículos relacionados:

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Autoreferenciar una tabla. Algunos ejemplos

Entendiendo las tablas autoreferenciadas

Usando una tabla autoreferenciada para averiguar números consecutivos faltantes

Restando fechas que se encuentran en distintas filas

El índice del blog Firebird21

El foro del blog Firebird21

Restando fechas que se encuentran en distintas filas

Deja un comentario

Un lector del foro de este blog, preguntó si era posible restar fechas que se encuentran en distintas filas.

Restar dos fechas de distintos registros

FECHAS01

Captura 1.

La respuesta es afirmativa, pero necesitamos conocer sin equivocación posible a la fila donde se encuentra la segunda fecha.

Por eso, en este caso supondremos que se encuentra en la siguiente fila. O sea, la Fecha Inicial estará en una fila y la Fecha Final siempre estará en la siguiente fila.

¿Y cómo podemos conocer cual es la siguiente fila?

Pues numerando a las filas, de tal manera que la primera tenga el número 1, la segunda tenga el número 2, la tercera tenga el número 3, etc.

Este artículo nos muestra algunas técnicas para conseguir lo que deseamos:

Numerando las filas de un SELECT

Lo que aquí haremos (no es el único método, pero es un método que se entiende bien) es agregarle una columna a nuestra tabla. Nos quedará así:

FECHAS02

Captura 2.

PRU_NUMREG es el Número de Registro

PRU_FECINI es la Fecha Inicial

PRU_FECFIN es la Fecha Final

Luego creamos un generador (también llamado secuencia) y con un comando UPDATE numeraremos a esas filas:

Listado 1.

CREATE GENERATOR
   GEN_NUMERAR;

UPDATE
   PRUEBA1
SET
   PRU_NUMREG = GEN_ID(GEN_NUMERAR, 1);

DROP GENERATOR
   GEN_NUMERAR;

Como el generador ya no nos sirve, lo eliminamos con el comando DROP.

El contenido de nuestra tabla ahora quedará así:

FECHAS03

Captura 3.

Como tenemos a todas las filas numeradas el siguiente paso ya es muy fácil:

Listado 2:

SELECT
   T1.PRU_NUMREG,
   T1.PRU_FECINI,
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1),
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1) - T1.PRU_FECINI AS DIFERENCIA
FROM
   PRUEBA1 T1

Podemos tener a todo junto:

Listado 3.

CREATE GENERATOR
   GEN_NUMERAR;

UPDATE
   PRUEBA1
SET
   PRU_NUMREG = GEN_ID(GEN_NUMERAR, 1);

DROP GENERATOR
   GEN_NUMERAR;

SELECT
   T1.PRU_NUMREG,
   T1.PRU_FECINI,
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1),
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1) - T1.PRU_FECINI AS DIFERENCIA
FROM
   PRUEBA1 T1;

Y este será el resultado que obtendremos:

FECHAS04

Captura 4.

Que es justamente lo que queríamos conseguir: restarle a la Fecha Inicial que se encuentra en una fila la Fecha Final que se encuentra en la siguiente fila.

Desde luego que este no es el único método posible para obtener este resultado, pero es un método fácil y sencillo de entender.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Agregando filas adicionales (2)

8 comentarios

Ya hemos visto en este artículo:

Agregando filas adicionales

como podemos hacer para agregar filas al resultado de salida de nuestra consulta. Para ello escribimos un stored procedure seleccionable.

Ahora, veremos otra técnica para obtener lo que deseamos, usando una construcción del Firebird llamada CTE (Common Table Expresion).

En nuestro ejemplo, la salida tiene 18 filas. ¿Por qué? porque hay 2 filas para Países, 4 filas para Estados y 12 filas para Ciudades. En total, 18 filas.

Y esas son exactamente las filas que tiene cada una de las tablas.

Eso nos trae a la mente que podemos usar el comando UNION para unir el conjunto resultado de la tabla PAÍSES con el conjunto resultado de la tabla ESTADOS y con el conjunto resultado de la tabla CIUDADES.

Listado 1.

WITH MiUnion AS (

   SELECT
      PAI_IDENTI * 1000000 AS CIU_ORDENX,
      PAI_NOMBRE           AS CIU_NOMPAI,
      ''                   AS CIU_NOMEST,
      ''                   AS CIU_NOMBRE
   FROM
      PAISES

   UNION

   SELECT
      EST_IDEPAI * 1000000 + EST_IDENTI * 1000 AS CIU_ORDENX,
      ''                                       AS CIU_NOMPAI,
      EST_NOMBRE                               AS CIU_NOMEST,
      ''                                       AS CIU_NOMBRE
   FROM
      ESTADOS

   UNION

   SELECT
      CIU_IDEPAI * 1000000 + CIU_IDEEST * 1000 + CIU_IDENTI AS CIU_ORDENX,
      ''                                                    AS CIU_NOMPAI,
      ''                                                    AS CIU_NOMEST,
      CIU_NOMBRE
   FROM
      CIUDADES

)

SELECT
   *
FROM
   MiUnion
ORDER BY
   CIU_ORDENX

Y este es el resultado que obtenemos al ejecutar el Listado 1.

CIUDADES10

 

Captura 1. Si haces clic en la imagen la verás más grande

Desde luego que la columna CIU_ORDENX no es necesario mostrarla a los usuarios, se muestra en la Captura 1. para que sea más fácil entender la técnica utilizada.

Explicación:

El comando UNION nos permite agregar al resultado de una consulta el resultado de otra consulta, obteniendo así un conjunto resultado que es la suma de los anteriores.

Como nuestras tablas tienen 2 filas, 4 filas y 12 filas, al hacer las UNIONes entre ellas el resultado final tendrá sí o sí, 18 filas.

La tabla MiUnion es una tabla virtual, una tabla que solamente existe en la memoria de la computadora, o sea, es una tabla CTE.

Como MiUnion es una tabla virtual puede tener todas las columnas que se nos ocurra, inclusive podemos inventarle columnas, tal como hicimos con la columna CIU_ORDENX.

La columna CIU_ORDENX utilizamos para guardar en ella el orden en el cual deseamos que las filas sean mostradas. Aquí obtenemos sus valores en potencias de 1.000 porque suponemos que las cantidades de Países, de Estados y de Ciudades serán menos que 1.000 y que sus identificadores respectivos también siempre serán menores que 1.000. Si cualquiera de ellos pueden ser más que 1.000 entonces tendríamos que usar potencias de 10.000, de 100.000, etc.

CIU_ORDENX para un País es: Identificador_del_País * 1.000 * 1.000

CIU_ORDENX para un Estado es: Identificador_del_País * 1.000 * 1.000 + Identificador_del_Estado * 1.000

CIU_ORDENX para una Ciudad es: Identificador_del_País * 1.000 * 1.000 + Identificador_del_Estado * 1.000 + Identificador_de_la_Ciudad

Esto implica que CIU_ORDENX para un País siempre terminará con 6 ceros, para un Estado siempre terminará con 3 ceros y para una Ciudad siempre terminará entre 001 y 999.

También implica que la cantidad de Países no puede ser mayor que 999, que la cantidad de Estados no puede ser mayor que 999 y que la cantidad de Ciudades no puede ser mayor que 999. Si cualquiera de ellos pudiera ser mayor que 999 entonces en lugar de usar potencias de 1.000 habría que usar potencias de 10.000, de 100.000, etc.

Luego de haber creado a la tabla virtual MiUnion lo único que nos resta es mostrarla, ordenada según la columna virtual CIU_ORDENX, y así obtenemos el resultado deseado.

Artículos relacionados:

Usando CTE (Common Table Expresion)

Agregando filas adicionales

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Older Entries