0

I want to replace the below-underlined character with the null value in oracle. I am using below query in Oracle. The query gives the values. But when i copy and paste it in Notepad, the same character is displaying in Notepad.

Query :

SELECT * FROM table1 WHERE column1 IS NOT NULL AND REGEXP_LIKE(column1, '[^\u0009\u000a\u000d\u0020-\uD7FF\uE000-\uFFFD]')

enter image description here

Can anyone help me on this?

Thanks in advance

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
Rajesh Chandrasekar
  • 103
  • 2
  • 12
  • It seems to me your regexp return TRUE if column1 contains any symbol different from `\u0009\u000a\u000d\u0020-\uD7FF\uE000-\uFFFD`. You are sure in this regexp? – AlexSmet Sep 16 '16 at 17:34
  • But still i can see the above character after running this query.. – Rajesh Chandrasekar Sep 16 '16 at 17:53
  • `REGEXP_LIKE` doesn't replace characters, it's work similar to `LIKE`. – AlexSmet Sep 16 '16 at 19:18
  • If you want to exclude rows where column1 contains any symbol from `\u0009\u000a\u000d\u0020-\uD7FF\uE000-\uFFFD` you need to change your query `SELECT * FROM table1 WHERE column1 IS NOT NULL AND NOT REGEXP_LIKE(column1, '[\u0009\u000a\u000d\u0020-\uD7FF\uE000-\uFFFD]')` – AlexSmet Sep 16 '16 at 19:27
  • Have a look at http://stackoverflow.com/questions/986826 and https://github.com/mysqludf/lib_mysqludf_preg – fabianegli Sep 16 '16 at 23:40
  • Not clear what you are trying to do. Do you want to display the rest of the string, just "delete" the "offending" characters? –  Sep 20 '16 at 03:32
  • Yes. I got the solution. I did this using below query "SELECT REPLACE(column,CHR(ASCII-char),'')". It works for me. Thanks for your comments – Rajesh Chandrasekar Sep 20 '16 at 06:35

0 Answers0