Archives of the TeradataForum

Message Posted: Fri, 25 Apr 2008 @ 09:59:27 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- 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.

Usage Notes

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.

Example

          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;

Anomy

	<Prev	Next>		<<First	<Prev	Next>	Last>>