CODEWITHSUNDEEP
sqloracle-databaseencoding
Anuket
Anuket

Reputation: 339

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 ¡. But this ? is wrong character in those words so maybe there are encoding problems. I think encoding is WIN1257

Upvotes: 0

Views: 664

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59563

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: 1252

C:\>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.

Upvotes: 1

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210952

try this:

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

example:

create table mytable(
  cell varchar2(100)
);

insert into mytable values('normal string');
insert into 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

Upvotes: 4

Related Questions