1

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Genhob
  • 13
  • 3
  • The regular expression can simply be `chr(9)` (not in quotes). What's the 'i' at the end in your attempt though? That's for case insensitive search; that doesn't apply to control characters. –  Jul 03 '19 at 14:53
  • I think you mean `chr(11)` – kfinity Jul 03 '19 at 14:54
  • Whatever the correct ASCII code is; I expect the OP will be able to find that out on his own. –  Jul 03 '19 at 14:57

2 Answers2

0

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
kfinity
  • 8,581
  • 1
  • 13
  • 20
0

You can also consider using asciistr() function within

regexp_like(clob_, asciistr(chr(to_number('0B','xx')))) to detect whether exists at least one vertical tab character ( Decimal : 11, Hexadecimal : 0B Char : ^K ) in the clob_ column

select id
  from tab
 where regexp_like(clob_, asciistr(chr(to_number('0B','xx'))))

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55