| Problems faced with TRANSLATE function [message #28239] |
Thu, 11 December 2003 00:12  |
Prabha R
Messages: 21
Registered: October 2001
|
Junior Member |
|
|
I have faced the following problem when buliding the query.
In the data base some junk character are there .
i have to remove those ans substitute with character say '-'.
i have used following query.
select translate(name,'¿','-') from table1 ;
or
select translate(name,chr(49855),chr(45)) from table1;
the above query is not giving any results.
then i tried with using DUAL table
select translate('Portal ¿ Por',chr(49855),chr(45)) xx from dual
xx
------------
Portal - Por
This was fetching me the results.
Then i tried with REPLACE function. This also doesnt work also with table columns.
So anyone please help me on this.
Thank you
|
|
|
|
|
|
|
|
| Re: Problems faced with TRANSLATE function [message #28248 is a reply to message #28243] |
Thu, 11 December 2003 03:46  |
Ultra
Messages: 98
Registered: November 2003
|
Member |
|
|
For me your char "the reversed question mark"
in your column table1.name is not change
it's because the ascii code that you are waiting for
is not foud.
Look at this :
select '¿' as col from dual; => '?'
select ascii('?') from dual; => 63
select ascii('¿') from dual; => 63
So my databse seems to be not able
to support this char .Each time I use '¿'
it's automatically changed to '?'
Maybe you should experiment some tests in that way.
|
|
|
|
| Re: Problems faced with TRANSLATE function [message #28249 is a reply to message #28239] |
Thu, 11 December 2003 03:54 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
CHR (191) and CHR (49855) look alike. Doing a cut and paste from your post and using the ASCII function, it looks like you have CHR (191) not CHR (49855):
scott@ORA92> select ascii (substr ('Portal ¿ Por', 8, 1)) from dual
2 /
ASCII(SUBSTR('PORTAL¿POR',8,1))
-------------------------------
191
scott@ORA92> select translate ('Portal ¿ Por', '¿', '-') xx from dual
2 /
XX
------------
Portal - Por
scott@ORA92> select translate ('Portal ¿ Por', chr (191), chr (45)) xx from dual
2 /
XX
------------
Portal - Por
scott@ORA92>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
