Stripping out special chars

dataservices job design
1

I have a need to strip out a special characters from a source column prior to loading the field into the target (both are Oracle).
The Oracle SQL that my company uses in other processes, and thus I am trying to match, is:

replace(replace(TRANSLATE(V_PART_DIM.PART_NBR,’~`!@#$%^&*()_-+={[}]|;:"?/.>,< ‘,’~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~’),’~’,’’),’’’’,’’)

Any advice on converting this to a function that DI understands?

Thanks,
Mark

mawalley (BOB member since 2006-10-17)

2

ADAPT00940564 created

That function slipped my attention. definitely something we should have for usability.

Right now will be to create a stored function in Oracle as a wrapper for the translate function and call that. Call it in a mapping if the function can be pushed down to the source database. Call it as stored procedure (“new function call”) otherwise.

Sorry.

Werner Daehn :de: (BOB member since 2004-12-17)

3

Thanks for the reply Warren. I will take your advice and call an Oracle function.

Definately something for a future release.

Thanks again,
Mark

mawalley (BOB member since 2006-10-17)

4

There seems to be still no solution for this. The only other solution - apart from the wrapped PL/SQL function - is to use the SQL transform:


SELECT REPLACE(TRANSLATE(column, '!@#$%^*', '_'), '_', NULL) FROM table

instead of a source table. Is this kind of function to be expected in a release in the nearby future? 2 years ago it was already ‘definitely something we should have for usability’… :yesnod: :nonod:

lamanp :netherlands: (BOB member since 2008-09-02)

5

:reallymad:

Werner Daehn :de: (BOB member since 2004-12-17)