1

I am trying to run a query in Oracle 11g where I am looking in a VARCHAR column for any rows that contain any of a carriage return, new line or tab. So far my code is as shown

select c1 from table_name where regexp_like(c1, '[\r\n\t]')

Not sure why but I am getting unexpected results. I saw some mention that Oracle doesnt support '\r' or any of the other characters I used? Some folks mentioned to use chr(10) for example and then I tried the following code

select c1 from table_name where regexp_like(c1, '[chr(10)|chr(13)]')

And again I am getting unexpected results. Pretty sure I am misunderstanding something here and I was hoping for some guidance.

rodneyc8063
  • 113
  • 8
  • 1
    Is your aim to remove them? Btw, where do you need, within such as SQL loader ..? – Barbaros Özhan Oct 27 '22 at 21:21
  • 1
    Does this answer your question? [How to enter newline character in Oracle?](https://stackoverflow.com/questions/5812148/how-to-enter-newline-character-in-oracle) – Nick Oct 27 '22 at 23:11
  • @BarbarosÖzhan - Eventually I may want to remove them but for now I would settle for just finding them. I think I may have my answer below though – rodneyc8063 Nov 08 '22 at 22:33
  • @Nick - Not exactly but it does help further support that I need to use the chr() function. Just not quite clear why though – rodneyc8063 Nov 08 '22 at 22:34

1 Answers1

2

You can use:

select c1
from   table_name
where  c1 LIKE '%' || chr(10) || '%'
or     c1 LIKE '%' || chr(13) || '%'
or     c1 LIKE '%' || chr(9) || '%';

or

select c1
from   table_name
where  regexp_like(c1, '[' || chr(10) || chr(13) || chr(9) || ']')

fiddle

where regexp_like(c1, '[\r\n\t]') does not work as you are matching any character that is \ or r or \ or n or \ or t (and not matching the perl-like character sets \r, \n or \t).

where regexp_like(c1, '[chr(10)|chr(13)]') does not wotk as you are matching any character that is c or h or r or ( or 1 or 0 or ) or | or c or h or r or ( or 1 or 3 or ) as you have a string literal and are not evaluating the contents of the literal. If you want to evaluate them as calls to the CHR function then it must be outside the string literal as the second example above.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • This totally helped clarify what I was looking for! I was totally confused on why even use the chr() function, and secondly I was getting lost in other peoples examples and no one broke down the problem like you did! I further improved on this code and added a '|' pipe delimiter between chr(10) etc as an explicit "or" - but this did the trick! Thank you! – rodneyc8063 Nov 08 '22 at 22:36
  • 1
    @rodneyc8063 If you are using it as `'[' || chr(10) || '|' || chr(13) || '|' || chr(9) || ']'` then that is wrong and you do not want the pipe character as does not act as an `OR` operator but instead would be matched as a character within the character set. – MT0 Nov 08 '22 at 22:39
  • Ugh that was exactly what I tried and it "seemed" to work (at least it didnt error). I did some reading and if I got it right the `[]` acts as an implicit `OR` already and I dont need the `|` delimiter it seems. Would that mean I could either use `'[' || chr(10) || chr(13) || chr(9) || ']'` or could I also use `chr(10) || '|' || chr(13) || '|' || chr(9)` ? I am guessing if I got it straight these two statements are equivalent? – rodneyc8063 Nov 10 '22 at 01:05
  • @rodneyc8063 Yes (although you might want round brackets around the second option if you are also matching anything before or after it) – MT0 Nov 10 '22 at 08:09