Help needed for an sql query

Tools & Utilities
Fan

Help needed for an sql query

I have a varchar column in a table which containts special characters like $,#,*,/ and so on, any number of times and anywhere in the column values.
I need to populate this column into another table by removing all such special characters and retain only alaphanumeric characters. Can anyone suggest how this can be done by using a normal sql update query instead of stored procedure?
5 REPLIES
Enthusiast

Re: Help needed for an sql query

I remember Oracle has such mechanism to replace or decode the characters. I hope the functions TRANSLATE and DECODE would solve the purpose.As far as Teradata is concerned, we do not have any such mechanism.But we can use other alternatives to do so. For eg:- you can use the UDF's to process the Character fields .

Enthusiast

Re: Help needed for an sql query

You can use CASE/SUBSTRING/POSITION functions to accomplish this.
You may also use Stored procedure but I won't recommend that.

I don't think there is equivalent of DECODE in teradata.

Vinay
Fan

Re: Help needed for an sql query

Vinay, I've tried using all the said functions. But they are good only to locate and eliminate individual character. But I'm not sure how many such characters are there in the column. So, the no.of iterations of the same logic is indeterminate. That's what I find undo-able about this with a single query.
I know this can be done using a stored procedure, but again, even I'm trying to work out an alternative.

Regards
Sree
Enthusiast

Re: Help needed for an sql query

You may want to try Recursive SQL functionality if you DBMS version is R6.

Vinay
Enthusiast

Re: Help needed for an sql query

Sri,

You can as well write a User defined function using C code to acheive your goal easily.I hope this will help you a lot.

Recursive query is also a good option. But, I recommend using UDFs.