Removing intermediate characters

[U]

Two part question.

We have data such as "57 092 448 518" and want a scalar function to remove the white space characters so that we can test it with a MATCHES_FORMAT check. Is there such a function? The TRIM() function only removes leading and trailing white space.

It may be that there are other characters such as "57:092-448-518". Is there a scalar function that can remove these? Convert() function looks likely, except that it acts like DataStage's Ereplace() function rather that performing character-by-character substitution.

Note that we are seeking a solution that can be implemented in Information Analyzer without any need to pre-process the data.

Thank you for your time.

[ray.wurlod]

Have you considered using MATCHES_REGEX to handle the zero-or-more "any" characters?