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

 

Listando las funciones externas

Deja un comentario

Como recordarás, Firebird nos permite usar funciones externas en nuestras bases de datos. Una función externa no está incluida en la instalación del Firebird sino que la agregamos después y a cada Base de Datos que la necesite utilizar. Eso significa que cada una de nuestras bases de datos puede tener cero, una, o varias funciones externas.

Si queremos saber cuales son las funciones externas que hemos registrado en una Base de Datos, podemos usar nuestro administrador gráfico para ello:

externa01

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

o podemos escribir un SELECT que nos de esa información:

Listado 1.

SELECT
   *
FROM
   RDB$FUNCTIONS
WHERE
   RDB$SYSTEM_FLAG = 0

Donde obtendremos un resultado similar al siguiente:

externa02

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

  • RDB$FUNCTION_NAME. Es el nombre con el cual se conoce a esta función dentro de nuestra Base de Datos
  • RDB$FUNCTION_TYPE. Tipo de la función, no está siendo usado y por eso siempre es Null
  • RDB$QUERY_NAME. Nombre de la consulta, no está siendo usada y por eso siempre es Null
  • RDB$DESCRIPTION. Comentarios que podemos escribir para describir lo que hace la función
  • RDB$MODULE_NAME. El nombre que tiene el archivo .DLL o el objeto compartido, en el disco duro u otro dispositivo de almacenamiento
  • RDB$ENTRYPOINT. El nombre que tiene la función externa en el archivo .DLL o en el objeto compartido. No siempre es igual a RDB$FUNCTION_NAME
  • RDB$RETURN_ARGUMENT. El número de posición del argumento que se devuelve, en la lista de argumentos de entrada
  • RDB$SYSTEM_FLAG. Una bandera que indica si la función fue definida internamente o externamente. 0=definida externamente, 1=definida internamente

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

 

 

 

Entendiendo los índices de expresiones

2 comentarios

Los índices sirven para dos cosas:

  1. Mostrar el resultado de los SELECTs ordenados por el criterio que nos interesa
  2. Realizar búsquedas o filtros, para que solamente sean afectadas las filas que cumplan con la condición que establecimos

Lo más común es que los índices estén compuestos por una o más columnas en forma directa. Veamos un ejemplo:

indices01

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

indices02

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

Tenemos una tabla ALUMNOS y para ordenar a los alumnos por APELLIDOS y por NOMBRES podríamos crear un índice como el siguiente:

Listado 1.

   CREATE INDEX IDX_ALUMNOS ON ALUMNOS(ALU_APELLD, ALU_NOMBRE);

Y está muy bien, funcionará perfectamente.

Podríamos escribir entonces un SELECT como el siguiente:

Listado 2.

SELECT
   *
FROM
   ALUMNOS
ORDER BY
   ALU_APELLD,
   ALU_NOMBRE

Y así obtendríamos un resultado como este:

indices03

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

Donde como puedes observar, los resultados aparecen ordenados por ALU_APELLD. Pero si queremos saber la diferencia entre ALU_TOTANO y ALU_TOTCOB no es posible usar un índice normal.

¿Y entonces?

Entonces la solución es crear un índice de expresión.

¿Qué es un índice de expresión?

Un índice en el cual se utiliza una expresión aritmética o una expresión alfanumérica o funciones internas.

Ejemplos de índices de expresión:

Listado 3.

   CREATE INDEX IDX_ALUMNOS2 ON ALUMNOS COMPUTED BY (LEFT(ALU_APELLD, 1) || LEFT(ALU_NOMBRE, 1));

   CREATE INDEX IDX_ALUMNOS3 ON ALUMNOS COMPUTED BY (ALU_TOTANO - ALU_TOTCOB);

Como puedes ver, la diferencia entre el índice creado en el Listado 1. y los índices creados en el Listado 3., es que en estos últimos se escribieron las palabras COMPUTED BY y también se usó la función LEFT() en IDX_ALUMNOS2 y una operación aritmética de resta en IDX_ALUMNOS3.

En todos los índices de expresión se deben escribir las palabras COMPUTED BY, tal como vimos en el Listado 3.

Usando índices de expresión

Algo muy importante a recordar es que cuando usamos índices de expresión debemos usarlos exactamente igual a como los definimos.

Listado 4.

SELECT
   *
FROM
   ALUMNOS
WHERE
   LEFT(ALU_APELLD, 1) || LEFT(ALU_NOMBRE, 1) = 'KM'

En este caso el Firebird usará el índice IDX_ALUMNOS2 porque la expresión escrita en la cláusula WHERE es la misma expresión usada en la definición del índice IDX_ALUMNOS2.

Listado 5.

SELECT
   *
FROM
   ALUMNOS
WHERE
   LEFT(ALU_NOMBRE, 1) || LEFT(ALU_APELLD, 1) = 'MK'

En el SELECT del Listado 5. el Firebird no usará el índice IDX_ALUMNOS2 porque la condición escrita en la cláusula WHERE no es igual a la expresión usada en la definición del índice IDX_ALUMNOS2.

Listado 6.

SELECT
   *
FROM
   ALUMNOS
WHERE
   ALU_TOTANO - ALU_TOTCOB > 1000

En el SELECT del Listado 6. el Firebird usará el índice IDX_ALUMNOS3 porque la condición escrita en la cláusula WHERE es la misma expresión escrita en la definición del índice IDX_ALUMNOS3.

Listado 7.

SELECT
   *
FROM
   ALUMNOS
ORDER BY
   ALU_TOTANO - ALU_TOTCOB

En el SELECT del Listado 7. también se usará el índice IDX_ALUMNOS3 porque la expresión escrita en la cláusula ORDER BY es la misma expresión que se usó en la definición del índice IDX_ALUMNOS3.

Conclusión:

Los índices de expresión pueden ser muy útiles en algunos casos, es bueno saber que contamos con esta herramienta para poder usarla cuando nos haga falta.

Artículos relacionados:

Optimizando un SELECT que compara columnas de la misma tabla (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

Optimizando un SELECT que compara columnas de la misma tabla (2)

6 comentarios

En este artículo:

Optimizando un SELECT que compara columnas de la misma tabla

vimos una técnica para optimizar los SELECT que comparan columnas de la misma tabla. La ventaja de esa técnica es que funcionará con cualquier motor SQL que utilicemos. Pero con Firebird tenemos además otra posibilidad, que es mejor que la anterior: usar índices de expresiones.

Nuestra tabla PRODUCTOS tiene la siguiente estructura:

optimizando1

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

Y podríamos crear un índice de expresión como el siguiente:

Listado 1.

CREATE INDEX IDX_PRODUCTOS ON PRODUCTOS COMPUTED BY (PRD_PREVTA - PRD_PRECTO);

Y nuestro SELECT tendría que ser así:

Listado 2.

SELECT
   *
FROM
   PRODUCTOS
WHERE
   PRD_PREVTA - PRD_PRECTO < 0

Donde la condición puesta en el WHERE tiene que ser igual que la expresión entre paréntesis en el Listado 1. Si no son iguales, el índice no será usado.

Si ahora miramos el rendimiento obtenido:

optimizando2

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

Veremos que efectivamente se ha usado el índice de expresión que creamos.

Las ventajas de usar un índice de expresión son:

  1. No necesitas crear una columna adicional
  2. No necesitas ejecutar un UPDATE para actualizar el contenido de la columna adicional
  3. No necesitas escribir un trigger que se dedique a actualizar el contenido de la columna adicional

Conclusión:

Usar un índice de expresión nos facilita el trabajo cuando necesitamos poner en la cláusula WHERE una condición que compara columnas, pero como todo índice hacemos trabajar más al motor cada vez que se realiza un INSERT, un UPDATE, o un DELETE en la tabla, así que debemos sopesar las ventajas y las desventajas de utilizarlo.

Artículos relacionados:

Optimizando un SELECT que compara columnas de la misma tabla

El índice del blog Firebird21

El foro del blog Firebird21

 

 

 

Optimizando un SELECT que compara columnas de la misma tabla

3 comentarios

En general, debemos tener a todas las columnas de todas nuestras tablas normalizadas. Eso es lo correcto y es lo recomendable. Sin embargo, hay ocasiones en que desnormalizar las columnas es conveniente.

Una de esas ocasiones es cuando debemos escribir un SELECT que en la cláusula WHERE compara el contenido de dos columnas. Veamos un ejemplo.

Tenemos la tabla PRODUCTOS con la siguiente estructura:

optimizando1

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

Y queremos saber si hay productos cuyo precio de venta es menor que su precio de costo, así que escribimos el siguiente SELECT.

Listado 1.

SELECT
   *
FROM
   PRODUCTOS
WHERE
   PRD_PREVTA < PRD_PRECTO

La consulta nos mostrará el resultado correcto, pero si analizamos su rendimiento, encontraremos que no ha usado un índice.

optimizando2

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

El problema es que no podemos tener un índice que pueda ser usado en casos como este. O sea, que no podemos tener índices para:

  • Comparar dos columnas de la misma tabla por =
  • Comparar dos columnas de la misma tabla por <
  • Comparar dos columnas de la misma tabla por >
  • Comparar dos columnas de la misma tabla por <=
  • Comparar dos columnas de la misma tabla por >=
  • Comparar dos columnas de la misma tabla por <>

Si la tabla tiene pocas filas, eso no es un problema, Firebird es muy rápido para devolver el resultado de los SELECT. Pero si la tabla tiene muchas filas, allí ya es otro tema.

¿Y cómo podemos hacer para mejorar la velocidad de nuestro SELECT?

La solución es crear una columna que contenga la diferencia entre las dos columnas que nos interesan. La estructura de la tabla PRODUCTOS quedaría entonces así:

optimizando3

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

Para mantener actualizada a la columna PRD_DIFERE podríamos escribir un trigger como el siguiente:

Listado 2.

CREATE TRIGGER PRODUCTOS_BIU FOR PRODUCTOS
   ACTIVE BEFORE
   INSERT OR
   UPDATE
   POSITION 1
AS
BEGIN

   NEW.PRD_DIFERE = NEW.PRD_PREVTA - NEW.PRD_PRECTO;

END;

Y para usar un índice entonces deberemos crearlo.

Listado 3.

CREATE INDEX IDX_PRODUCTOS ON PRODUCTOS(PRD_DIFERE);

Y si ahora escribimos el SELECT del Listado 1. modificado para que utilice a la columna PRD_DIFERE, tendríamos:

Listado 4.

SELECT
   *
FROM
   PRODUCTOS
WHERE
  PRD_DIFERE < 0

Queremos verificar si ahora se está usando un índice, así que miramos el rendimiento y encontramos:

optimizando4

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

Y comprobamos que sí, efectivamente ahora se usa un índice, y por lo tanto nuestro SELECT será mucho más rápido que antes.

Conclusión:

En general debemos tener a todas las columnas de todas nuestras tablas normalizadas, pero hay excepciones, como el caso mostrado en este artículo. Eso se debe a que el Firebird no utiliza índices cuando comparamos el contenido de una columna con el contenido de otra columna. La solución es crear una columna adicional que contendrá la diferencia entre los valores de las columnas que necesitamos comparar.

Desde luego que comparar precio de costo con precio de venta es sólo un ejemplo. También podemos comparar importe vendido contra importe cobrado, importe comprado contra importe pagado, etc.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

EMS SQL Manager ha sido actualizado para Firebird 3

3 comentarios

El programa administrador de bases de datos EMS SQL Manager desde el 9 de noviembre de 2016 ya ofrece soporte para las características y mejoras que fueron introducidas con Firebird 3.

ems01

Esto facilitará la migración de las bases de datos a Firebird 3 a aquellos que están acostumbrados a trabajar con este administrador gráfico.

Artículos relacionados:

El índice del blogFirebird21

El foro del blog Firebird21

 

Firebird Stored Procedure Generator

5 comentarios

Si te aburre escribir stored procedures y te gustaría automatizar esa tarea, puedes usar para ello un programa gratuito llamado Firebird Stored Procedure Generator, el cual puedes descargar desde:

http://www.sieradelta.com/Products/FBSPGen.aspx

Los comandos que puedes automatizar son:

  • INSERT
  • UPDATE
  • UPDATE OR INSERT
  • DELETE
  • SELECT
  • SELECT ALL
  • SELECT PAGE
  • COUNT

Cuando ejecutes al programa, después de descargarlo e instalarlo, verás una pantalla similar a la siguiente:

fspg01

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

Al hacer clic sobre el botón “Next”, verás la siguiente pantalla, en la cual deberás ingresar los datos de la Base de Datos con la cual deseas trabajar:

fspg02

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

Para asegurarte que has escrito bien los parámetros de conexión puedes hacer clic en el botón “Test Connection”. Si la conexión puede realizarse exitosamente, verás una pantalla similar a la siguiente:

fspg03

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

Si luego haces clic sobre el botón “Next”, verás una pantalla similar a la siguiente:

 

fspg04

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

Al hacer clic en el botón “Load Objects” le dirás que cargue todas las tablas de tu Base de Datos para que pueda luego utilizarlas en los stored procedures que te generará el programa.

fspg05

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

En este caso se seleccionó la tabla CUENTAS y por eso nos mostró todas las columnas de la tabla CUENTAS. El siguiente paso es seleccionar las columnas por las cuales se puede ordenar la tabla y seleccionar el generador (secuencia) que le corresponde a esta tabla.

fspg06

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

 El siguiente paso es decirle cuales son los prefijos que nos gustaría usar para nombrar al stored procedure, a sus variables, y a sus parámetros. Desde luego que podremos cambiarlos más adelante si no nos gustan.

fspg07

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

Luego, elegimos el tipo de stored procedure que queremos generar:

fspg08

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

Luego, si lo deseamos, podemos escribir los nombres de los usuarios y los nombres de los roles que tendrán permiso para ejecutar al stored procedure que estamos creando. Debemos escribir un usuario o un rol en cada línea:

fspg09

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

A continuación generaremos el stored procedure haciendo clic sobre el botón “Generate”.

fspg10

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

Si deseamos copiar el stored procedure en el portapapeles, hacemos clic sobre el botón “Copy”.

Si queremos guardar el stored procedure en un archivo de script, hacemos clic sobre el botón “Save”.

Finalmente, en la siguiente pantalla, si deseamos guardar la configuración para un uso futuro,  hacemos clic sobre el botón “Save”

fspg11

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

Conclusión:

Escribir stored procedures largos puede ser bastante tedioso. Gracias a este programa: Firebird Stored Procedure Generator podremos realizar esa tarea mucho más rápidamente y sin aburrirnos.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries Newer Entries