Ejemplo de recursión (7). Números faltantes en una serie

2 comentarios

Si en una tabla o en una vista o en un procedimiento almacenado seleccionable, tenemos una columna numérica y queremos saber si están todos los números o si falta alguno, podemos usar la técnica mostrada en este artículo para averiguarlo.

Para ello, mediante recursión crearemos una tabla virtual, que en nuestro ejemplo llamaremos RANGO_NUMEROS. Esa tabla virtual contendrá todos los números que nos interesan, de forma consecutiva. Es decir: 1, 2, 3, 4, 5, 6, 7, 8, 9, …

Desde luego que podemos empezar con cualquier número, no es obligatorio que empecemos con el número 1.

La tabla es virtual porque solamente existe en la memoria de la computadora y mientras dure la ejecución del SELECT principal, no existe dentro de la Base de Datos ni tampoco se guarda en el disco duro, solamente existe hasta que el SELECT principal finaliza, luego … desaparece totalmente.

Listado 1. Un SELECT para averiguar si hay números consecutivos faltantes

WITH RECURSIVE RANGO_NUMEROS AS (

   SELECT
      1 AS NUMERO
   FROM
      RDB$DATABASE

   UNION ALL

   SELECT
      NUMERO + 1 AS NUMERO
   FROM
      RANGO_NUMEROS
   WHERE
      NUMERO <= 36
)

SELECT
   NUMERO
FROM
   RANGO_NUMEROS
LEFT JOIN
   REVALUOSCAB
      ON NUMERO = RVC_IDENTI
WHERE
   RVC_IDENTI IS NULL

Como siempre que usamos recursión, debemos asignar el valor inicial (en nuestro ejemplo, es el número 1, pero puedes elegir otro número si quieres) y un valor final (en nuestro ejemplo, es 36).

El valor final es absolutamente necesario establecerlo porque de lo contrario la recursión continuaría indefinidamente. Bueno, en realidad, hasta que llegues al límite de recursiones permitidas o hasta que la computadora se quede sin memoria RAM.

Siempre que uses recursión debes establecer una condición de salida, es decir, una condición para que la recursión finalice. No tendría sentido de otro modo.

¿Que hicimos en el Listado 1.?

Primero, hemos creado una tabla virtual llamada RANGO_NUMEROS, cuyo contenido es una sola columna, llamada NUMERO, y cuyos valores van desde el 1 hasta el 37 de forma consecutiva, es decir sin que falte algún número. Están todos. Va hasta el 37 porque en el SELECT pusimos NUMERO + 1. Y como en el WHERE pusimos 36, entonces obtendremos un número más, en este caso 37.

Segundo, hemos hecho un LEFT JOIN de nuestra tabla virtual llamada RANGO_NUMEROS con la tabla REVALUOSCAB, la cual tiene los números que queremos verificar.

Tercero, pusimos la condición RVC_IDENTI IS NULL para que solamente nos muestre los números que están en la tabla virtual RANGO_NUMEROS y que no están en la tabla REVALUOSCAB. De esta manera, solamente los números que se encuentren en la tabla virtual RANGO_NUMEROS y que no se encuentren en la tabla REVALUOSCAB obtendremos en el resultado.

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

En la Captura 1. vemos el contenido de la columna RVC_IDENTI de la tabla REVALUOSCAB. Como puedes notar, faltan los números que van del 26 al 36.

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

Después de ejecutar el Listado 1. obtenemos como resultado lo que vemos en la Captura 2., es decir, todos los números faltantes.

Conclusión:

Hay varias técnicas para mostrar los números que faltan en una serie, en este artículo hemos visto una de esas técnicas, la cual emplea recursión. Saber usar recursión puede ayudarte en muchos casos, por lo tanto es muy bueno conocer como usarla.

Artículos relacionados:

Stored procedures recursivos

Entendiendo a las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

Usando recursividad con CTE

Ejemplo de recursión (1). Filas salteadas

Ejemplo de recursión (2). Numerar filas

Ejemplo de recursión (3). Fechas consecutivas

Ejemplo de recursión (4). Actualizando filas recursivamente

Ejemplo de recursión (5). Saldos acumulados

Ejemplo de recursión (6). Repitiendo las filas

El índice del blog Firebird21

El foro del blog Firebird21

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

 

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

 

 

Verificando si un string es un número válido

2 comentarios

A veces podemos necesitar saber si en una columna de tipo CHAR o VARCHAR se ha guardado un número válido.

¿Cómo lo conseguimos rápidamente?

Usando el predicado de comparación SIMILAR TO.

Ejemplo 1:

En la tabla de ALUMNOS tenemos una columna donde guardamos el número de la matrícula. Queremos verificar que solamente haya números válidos allí.

NUMEROS1

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

Listado 1.

SELECT
   ALU_MATRIC,
   ALU_NOMBRE
FROM
   ALUMNOS
WHERE
   ALU_MATRIC SIMILAR TO '[0-9]*'

NUMEROS2

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

Ejemplo 2:

Queremos saber cuales son los alumnos cuyo número de matrícula es incorrecto. Para ello solamente agregamos NOT, como podemos ver en el Listado 2.

Listado 2.

SELECT
   ALU_MATRIC,
   ALU_NOMBRE
FROM
   ALUMNOS
WHERE
   ALU_MATRIC NOT SIMILAR TO '[0-9]*'

NUMEROS3

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

Si lo que nos interesa es saber cuantos alumnos tienen número de matrícula correcto o número de matrícula incorrecto entonces en lugar de seleccionar la Matrícula y el Nombre usaríamos la función agregada COUNT().

Ejemplo 3:

En la tabla FACTURAS tenemos datos de las Facturas de venta.

NUMEROS4

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

Por alguna razón que no sabemos, la columna FAC_COBRAD es de  tipo VARCHAR, no es numérica. Por eso ahora queremos verificar que todos los números allí escritos sean números válidos.

Listado 3.

SELECT
   FAC_NUMERO,
   FAC_FECVEN,
   FAC_COBRAD
FROM
   FACTURAS
WHERE
   TRIM(FAC_COBRAD) SIMILAR TO '[\+\-]?[0-9]*.?[0-9]*' ESCAPE '\'

NUMEROS5

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

Explicando el patrón usado en SIMILAR TO

NUMEROS6

Conclusión:

Saber si en una columna de tipo CHAR o VARCHAR hay solamente números válidos puede ser muy útil muchas veces, en este artículo se mostró una técnica para realizar esa verificación.

Artículos relacionados:

Los predicados de comparación

Usando SIMILAR TO

El índice del blog Firebird21

El foro del blog Firebird21

 

Extrayendo caracteres del WHERE

Deja un comentario

Lo correcto es no guardar en las tablas los datos con cualquier formato, sino guardarlos solamente con el formato que previamente hemos establecido.

Sin embargo a veces podríamos encontrarnos con la situación de que el formato es desconocido.

Ejemplo:

Un usuario podría escribir: 123-456-789

Otro usuario podría escribir: 1-23-456-7-89

Otro usuario podría escribir: 12-345678-9

Otro usuario podría escribir: 123456789

Entonces, ¿qué condición de filtro debemos escribir en el SELECT para que sea cual sea el formato podamos obtener el dato que nos interesa?

Listado 1.

SELECT
   *
FROM
   MiTabla
WHERE
   REPLACE(MiColumna, '-', '') = 'MiValorBuscado'

Comentario 1:

Aunque como hemos visto podemos escribir un SELECT que nos muestre todas las filas que tienen el valor buscado, hay un error intrínseco de diseño porque estamos permitiendo que los usuarios ingresen los datos como se les antoja, eso no debería ser así.

Para estos casos una posibilidad es tener un trigger BEFORE INSERT OR UPDATE que se encargue de extraer los guiones antes de guardar la fila en la tabla, algo como:

Listado 2.


New.MiColumna = REPLACE(New.MiColumna, '-', '')

Comentario 2:

Aunque el Listado 1. funcionará bien y nos mostrará lo que deseamos ver, si la tabla es muy grande puede llegar a ser muy lento, demorarse una eternidad. ¿Por qué? Porque no se usará un índice y reemplazar un carácter por otro no es instantáneo. Por ese motivo es preferible guardar la columna sin los guiones, como se muestra en el Listado 2. Pero … ¿y si necesitamos a veces mostrarle al usuario la columna exactamente igual a cómo él la había escrito?

Una solución es agregarle una columna a la tabla, y entonces tendremos dos columnas:

Una columna, para guardar en ella lo que escribió el usuario

Otra columna, para guardar en ella lo que escribió el usuario pero sin los guiones

Esta segunda columna podríamos obtenerla mediante un trigger similar al del Listado 2. o mediante una columna computada, algo como:

Listado 3.


MiColumnaEditada COMPUTED BY (REPLACE(New.MiColumna, '-', ''))

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Older Entries