• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2853
  • Last Modified:

EXAMPLE OF TRANSLATE

I need an example of Oracle Translate function that strpits all psecial characters from the string and leavs only letters and numbers.
example translate '12-98*A-a(jj' to '1298Aajj'
0
smena
Asked:
smena
3 Solutions
 
grant300Commented:
You want the REPLACE function as opposed to the TRANSLATE function.

TRANSLATE does an ASCII code for ASCII code translation.

REPLACE will actually replace the charactors you don't want with nothing.

Example:

 v_string := REPLACE(v_string,'!@#$%^&*(){}[]\|`~.,/?/_=')

will strip all of the characters in the second argument from v_string.

If you have trouble getting all of the "special" characters into a quoted string, you might have to build a string variable containing all of the printable ones concatinated with CHR(xx)||CHR(yy)|| etc.  This is invariably easier than escaping quotes and other such nonsense.  You can wrap that definition as wiell as the REPLACE function into a user-defined function and call it whereever you need to.

Bill.
0
 
MikeOM_DBACommented:
Just put space ' ' plus the characters you want to remove in the first string and space ' ' in the secong string:

TRANSLATE(Col1, ' `~!@#$%^&*()_+={}[]|\":;<>?/', ' ')

0
 
mudumbeCommented:
If you want to get rid of the blanks, you can use any character in the first position for eg:

TRANSLATE(Col1, 'x`~!@#$%^&*()_+={}[]|\":;<>?/', 'x')

This is because, the last 2 arguments to translate cannot be null.

You can also say:

TRANSLATE(Col1, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')
0
 
grant300Commented:
mudumbe:

I am pretty certain TRANSLATE is not exclusive.  That is, if you don't specify a character in the second argument, it won't be changed at all.

Bill
0
 
rkogelheCommented:
Bill's right... but a more generic solution is to say:

translate(col1,'X'||translate(col1,'-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789','-'),'X')

so in other words, remove all the good characters to find the bad ones, then use that as the parameter of the characters we want to remove.

SQL> select
  2  translate('12-98*A-a(jj' ,'X'||translate('12-98*A-a(jj' ,'-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789','-'),'X')
  3  from dual;

TRANSLAT
--------
1298Aajj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now