Archives of the TeradataForum
Message Posted: Fri, 25 Apr 2008 @ 09:59:27 GMT
<-- Anonymously Posted: Fri, 25 Apr 2008 02:54 -->
A UDF is the right way to go, and the TRANSLATE UDF from the Oracle UDF package on teradata.com should do, assuming you can define the valid characters, otherwise a slightly modified version of TRANSLATE would suffice.
Extract from UDF definition:
Returns char with all occurrences of each character in from replaced by its corresponding character in to, where char, from, and to are string arguments.
Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value.
You cannot use an empty string for to to remove from the return value all characters in from. TRANSLATE interprets the empty string as null, and if this function has a null argument, it returns null.
SELECT TRANSLATE('2KRW229', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "Licence" FROM DUAL;
So you could do something like:
SELECT TRANSLATE(your_column', '0123456789< add all other possible characters here >', '0123456789') FROM your_table;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|