Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2850
  • 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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