Oracle Sql Find Character ¡ In Data

Is it possible to find this underscore i ¡ character in database records? If I do like this select * from mytable where cell like '%¡%'; it founds rows, where ? is present, not

Solution 1:

try this:

select * from mytable whereinstr(cell, UNISTR(<UNICODE code of your character>))>0;

example:

createtable mytable(
  cell varchar2(100)
);

insertinto mytable values('normal string');
insertinto mytable values('fünny string');
commit;

select*from mytable where instr(cell, UNISTR('\00fc'))>0;

Output:

CELL
-----------------------------------------------------------------------------------------------
fünny string

1 row selected.

Edited: like @Wernfried Domscheit recommended i've changed CHR --> UNISTR, - indeed this should work with any character set

Solution 2:

Assuming you are using SQL*Plus you have to set your code page and your NLS_LANG value accordingly.

C:\>chcp 1252
Active code page: 1252C:\>set NLS_LANG=.WE8MSWIN1252
C:\>sqlplus ...

SQL> select * from mytable where cell like'%¡%';

Note, Windows Codepage1257 does not have character "¡". You must change the code page to chcp 1252 or chcp 28591 for ISO-8859-1 for instance.