Search code examples
sqlregexoracle-databaseregexp-like

Oracle REGEXP_LIKE find vertical tab 0xb character

Trying to find a vertical tab character in a CLOB field. Using regexp_like which doesn't support entry of hex characters. so this is not valid and REGEXP_LIKE(,'[\xB]','i') Posix entries don't seem to help as :cntl: includes items like carriage return/new line which is valid in my scenario.

Solution

  • There's two standard ways of specifying non-printing characters in Oracle - chr() for the 8-bit codepages and unistr() for UTF-16. Here's an example that shows both of them (vertical tab is decimal 11 for 8-bit and \000B for UTF-16).

    with test as (select to_clob(unistr('\000B')) as str from dual)
select 'Found' from test
where regexp_like(str, chr(11),'i'); -- case insensitivity doesn't matter unless you're using letters