otranslate и пример запроса для 13 версии Teradata.


Работает в VERSION 13.
------------------------------------------------------------------------------------------------------------
CREATE SET TABLE DEV_DB.TABLE
(
ID_N INTEGER NOT NULL,
C_TXT VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC
)
UNIQUE PRIMARY INDEX TABLE_UPI ( ID_N );
------------------------------------------------------------------------------------------------------------
INSERT INTO DEV_DB.TABLE (ID_N,C_TXT) VALUES (0,'0091**34/2');
INSERT INTO DEV_DB.TABLE (ID_N,C_TXT) VALUES (1,'1A234-5 6*');
INSERT INTO DEV_DB.TABLE (ID_N,C_TXT) VALUES (2,'2--3*39*#&');
INSERT INTO DEV_DB.TABLE (ID_N,C_TXT) VALUES (3,'3K56M660D.');
INSERT INTO DEV_DB.TABLE (ID_N,C_TXT) VALUES (4,'4WS55K 333');
INSERT INTO DEV_DB.TABLE (ID_N,C_TXT) VALUES (5,'5OP9934A10');
------------------------------------------------------------------------------------------------------------

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 DEV_DB.TABLE 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') --
AND ID_N =0
) pre
WHERE ID_N =0
GROUP BY ID_N
ORDER BY ID_N

----------------------------------------------------------------------------------------------------
--------
0 91342.0
1 123456.0
2 2339.0
3 356660.0
4 455333.0
5 5993410.0
------------------------------------------------------------------------------------------------------------
Работает в VERSION 14.

select top 100
ID_N
,C_TXT
,otranslate(C_TXT,'`' || otranslate(C_TXT, '`1234567890', '`'), '`')
from DEV_DB.TABLE
------------------------------------------------------------------------------------------------------------
0 91342.0
1 123456.0
2 2339.0
3 356660.0
4 455333.0
5 5993410.0