Geek asks and answers

In Oracle, how do I select strings containing a character within a specific number range?

I have a table in Oracle with a VARCHAR column called DESCRIPTION. Some lines contain non-printable characters, such as a character with a numeric value of 150 (which is not in Latin-1 and is the "beginning of a protected zone" in Unicode).

I want to select all rows whose DESCRIPTION columns contain a character whose numeric value is between 128 and 160. Is there a way to do this without a long list of LIKE OR'ed sentences together? I assume that this can be done using regular expressions, but I have not found a way to do this.

+3
source share
3 answers

- , SQL:

with codes as (select rownum code from dual connect by level <= 160)
select distinct t.id, t.description
from mytable t, codes c
where t.description like '%' || chr(c.code) || '%'
and c.code >= 128;
+5

Vincent ! ASCII: 128-255, :

SELECT description
FROM your_table
WHERE regexp_like (description, '['||chr(128)||'-'||chr(255)||']');

.

+1

You can use a regex, it may work better than 30 + one WHERE clause, but it will not be much prettier:

SELECT * 
  FROM your_table 
 WHERE regexp_like(description, '['||chr(128)||chr(129)||...||chr(160)||']')
0
source

More articles:


All Articles