Hi,
I am having certain extended special characters in one of my field 'Name' which is stored in a clob column. I am trying to put checks
so that the 'Name' field only contains ASCII OR extended ASCII characters(1-255). For this I have put the following regexp in my select
regexp_like ('HiNáME', '['||chr(1)||'-'||chr(255)||']');
The above does not work though. On further investigating , I can see that character 'á' is causing the issue , this should ideally have a ASCII
of 225 but when I run
select ascii('á') from dual; this displays 50081 (on SQL develeoper and Pl/sql developer as well)
which is causing my regexp_like to break and ignore the valid Name. Also I tried to check ascii of
this character from Pycharm editor and it shows correctly as 225.
My question is
1) is there any easier way to identify all strings having only ascii and extened ascii character (1-255 range)
2) Why does this 'á' show incorrect ascii which is 50081 on sql or pl/sql editor.
My oracle version -
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
NLS parameters -
nls_language - AMERICAN
nls_territory - AMERICA
nls_sort - NULL
nls_comp - NULL
nls_length_semantics - BYTE
nls_nchar_conv_excp - FALSE
Please let me know anything else required