Esta es una pregunta que aparece una y otra vez en los foros: ¿Cómo puedo eliminar de una cadena todos los caracteres que no sean números? Bien, lo primero que viene a la cabeza es usar oTRANSLATE(), pero tiene como inconvenientes que:
1. Es una UDF (externa, a veces no instalada… -al menos hasta TD14.0-)
2. Debemos incluir en la cadena de caracteres a eliminar todas las posibles ocurrencias (lo cual no es moco de pavo).
Bajo ciertas circunstancias se puede buscar una solución ‘pure SQL‘ que no sea pesada en exceso. Para que eso ocurra la cadena a ‘limpiar’ no debería ser muy larga y deberíamos evitar el uso de ‘queries’ recursivas.
Para evitar recursividades, vamos a tomar una aproximación parecida a la que utilizamos en «Factorial sin función recursiva, sólo SQL»: vamos a usar las matemáticas.
Digamos que tenemos una tabla con la siguiente estructura:
BTEQ -- Enter your SQL request or BTEQ command:
SHOW TABLE CARLOS.PRUEBA05;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
--------------------------------------------------------------
CREATE SET TABLE CARLOS.PRUEBA05 ,
NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
ID_N INTEGER NOT NULL,
C_TXT VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX PRUEBA05_UPI ( ID_N );
La existencia de una PK (aquí en forma de UPI) es muy importante, ya que vamos a ‘desensamblar’ y luego ‘volver a ensamblar’ el dato basándonos en ella.
Cargamos las cadenas con números y otros caracteres:
BTEQ -- Enter your SQL request or BTEQ command:
SELECT *
FROM CARLOS.PRUEBA05
ORDER BY ID_N;
*** Query completed. 6 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
ID_N C_TXT
----------- ----------
0 0091**34/2
1 1A234-5 6*
2 2--3*39*#&
3 3K56M660D.
4 4WS55K 333
5 5OP9934A10
Bueno, pues ahora viene la parte en la que usamos la cabeza: vamos a desensamblar la cadena letra por letra, desechar los caracteres no numéricos y los vamos a volver a ensamblar basándonos en su posición relativa mediante potencias de 10 con un SUM().
BTEQ -- Enter your SQL request or BTEQ command:
SELECT ID_N,
SUM(CAST(LETRA AS INTEGER)*10**(RN-1)) (FORMAT 'ZZZZZZZZZ9') ONLY_NUMBERS
FROM (
SELECT a.ID_N,
b.iLETRA,
ROW_NUMBER() OVER (PARTITION BY a.ID_N ORDER BY b.ILETRA DESC) RN,
SUBSTR(a.C_TXT,b.iLETRA,1) LETRA
FROM CARLOS.PRUEBA05 a,
( SELECT day_of_calendar iLETRA
FROM SYS_CALENDAR.CALENDAR
WHERE day_of_calendar < 11 ) b --Longitud de la cadena!!
WHERE LETRA IN ('0','1','2','3','4','5','6','7','8','9')
) pre
GROUP BY ID_N
ORDER BY ID_N
;
*** Query completed. 6 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
ID_N ONLY_NUMBERS
----------- ------------
0 91342
1 123456
2 2339
3 356660
4 455333
5 5993410
Hay que tener en cuenta que si en la cadena tenemos ceros que aparecen como los primeros caracteres numéricos, no podremos recuperarlos fácilmente -a no ser que fijemos el formato de salida (con FORMAT)- ya que estamos convirtiendo los caracteres a NÚMEROS y utilizando éstos como base del cálculo. Esto es un engorro para códigos numéricos en los que los ceros por la izquierda tienen valor, y además puedan tener logitud variable, pero para muchos otros casos la cosa puede valer muy bien. Tampoco podremos identificar así valores positivos y negativos (con un signo + ó – al principio) o los valores decimales por las mismas razones: eliminamos signos y cualquier signo de puntuación.
Por otra parte, en las condiciones indicadas la ‘query‘ se comporta razonablemente bien: utilizando este algoritmo, una ‘query‘ consiguió extraer los números de una columna de 9 caracteres sobre una tabla de unas 3.500.000 filas en 1,5 minutos en un modesto entorno de desarrollo, aunque los resultados pueden variar mucho de sistema a sistema.
Saludos.
Carlos.
CarlosAL 