remove "whitespaces" and "." in a string

Analytics
Enthusiast

remove "whitespaces" and "." in a string

I have a table with a column of alpha-numeric data the currently has multiple (periods) "." and spaces in the string.

I need to remove them for data compare in another DBMS.

I have searched not only through this forum but others as well and can not seem to find the solution.

Note: orereplace is not an option as it is not installed and have no way to get it installed.

Here are a few examples of how the data is currently formated:

99.ABCD.123456..ABCD

77.ABCD.654321.  .DCBA[SPACE]

.EFGH.666555...DCFE

The periods are not always in the same place so any counting method will not work.

Any suggestions?

Thanks for taking the time to look.

Regards,

3 REPLIES
Senior Supporter

Re: remove "whitespaces" and "." in a string

UDFs will do this most efficient but 

http://forums.teradata.com/forum/database/change-all-periods-in-a-column-to-forward-slash#comment-20...

might outline an way how to do it the hard way.

Enthusiast

Re: remove "whitespaces" and "." in a string

Thanks ulrich - I saw that you had replyed to a previous post of mine on this issue. I will try this method and respond back soon.

Thanks again for looking.

Enthusiast

Re: remove "whitespaces" and "." in a string

Hi All,

Please download the UDF from below link and just install two UDF under replace folder.

http://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-oracle-functions

There is two UDF oreplace1 and oreplace2 using which we have accomplish the same function. Please install the both UDF under syslib database in teradata and it works fine.

Regards

Pinal Patel