SQL & PL/SQL

Hi !

I would like to find strings with nbsp (non-breaking spaces) in our database. For this purpose I have tried several implementation using either like or regexp_like but the result haven't matched my expectations.
Below there are a couple of select statements showing what is returned using chr(160) in like or regexp_like and what I have expected.



select * from
(
select chr(160) || 'km' text from dual
union all
select chr(32) || 'km' text from dual

) where regexp_like(text, chr(160))
/*
TEXT
-------

0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
*/
Expected result = 1 record



select * from
(
select chr(160) || 'km' text from dual
union all
select chr(32) || 'km' text from dual

) where regexp_like(text, chr(32))
/*
TEXT
-------
km
*/
Works fine in this case

select * from
(
select chr(160) || 'km' text from dual
union all
select chr(32) || 'km' text from dual

) where text like '%' || chr(160) || '%'
/*
TEXT
-------
km
km
*/
Expected result 1: record (got 2). Why ?


select * from
(
select chr(160) || 'km' text from dual
union all
select chr(32) || 'km' text from dual

) where regexp_like(text, chr(160) || '.*')
/*
TEXT
-------
km
km
*/
expected result 1 record

BTW the database is configured as follow:

NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_LENGTH_SEMANTICS CHAR

We are using Oracle 10g.

Is there a way to avoid expending strings using asciistr() before doing regexp or like searches (there is a performance issue) ? Are like and regexp not working properly with ascii > 127 ?
Can somebody explain the behaviour shown above ?

Thanks for help.