Hi experts,
I am having a bit of a problem with the convert function. We use convert to compare street- and citynames and ignore any special characters, such as é ç etc.
Something like:
select ...
from ...
where convert(new_street_name, 'US7ASCII') = convert(existing_street_name, 'US7ASCII')
This works fine if the datatype is varchar2, for instance:
SQL> select convert('äàáâçëèéêïìíîöòóôüùúûÿ','US7ASCII') text from dual;
TEXT
----------------------
aaaaceeeeiiiioooouuuuy
If the datatype if nvarchar2 however, the result is not as expected:
SQL> select convert(cast('äàáâçëèéêïìíîöòóôüùúûÿ'as nvarchar2(64)),'US7ASCII') text from dual;
TEXT
--------------------------------------------------------------------------------
慡慡捥敥敩楩楯潯潵畵
The NLS character settings on our database (10.2.0.4) are:
NLS_CHARACTERSET AL32UTF8 Character set
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
I have tried several combinations... but no luck so far. Is it possible to use convert on an nvarchar2 to go from é to e?
Maybe it is better just to use the translate function and define each conversion explicitly. Convert seemed a nice option because it works without any additional parameters... on a varchar2 at least
Thanks!