Oracle translate function

The project requires special handling of military ID card, as long as the digit. I wanted to use a regular expression, but oracle9i does not support regular expressions.

Later, we found that translate can implement functions.

Translate (string, from_str, to_str) replace the value in string by character. Some characters appear in from_str (to be replaced), but to_str (to be replaced) does not have the corresponding replacement character, oracle automatically replaces this character with NULL.

Translate (exp, x | exp, x). In this way, the x characters in exp are retained, and other characters are automatically removed.