0

I have a table EMPLOYEE with a column called LAYOUT. LAYOUT is a CLOB column

How can I check if the LAYOUT contains any characters that are not ASCII? Ideally I would like to see the rows that contain the non-ASCII character

user906153
  • 1,218
  • 8
  • 30
  • 43
  • 2
    Have a look at this: https://stackoverflow.com/questions/50914930/find-out-if-a-string-contains-only-ascii-characters `CONVERT` works even with CLOB – Wernfried Domscheit Jul 17 '18 at 17:07
  • 1
    I'm not saying you do but I see the terms "ASCII" and "Unicode" misused often. It'd be interesting to know your ultimate goal in case there's a more direct approach. – Álvaro González Jul 17 '18 at 17:38
  • @WernfriedDomscheit - I was unable to get the `CONVERT` method to work. I'm getting a inconsistent datatype error when selecting `select * from employee where layout != CONVERT(layout, 'US7ASCII');` – user906153 Jul 17 '18 at 17:46
  • Strange, according [documentation](https://docs.oracle.com/database/121/SQLRF/functions041.htm#SQLRF00620) `CLOB` is supported as input and output. Perhaps you have to process chucks of your CLOB in a loop. – Wernfried Domscheit Jul 17 '18 at 17:55
  • @WernfriedDomscheit - Putting it into a loop did the trick. No errors there – user906153 Jul 17 '18 at 18:03

1 Answers1

1

I was able to figure it out with the help of comments on the question

Begin
    for empData in (select * from EMPLOYEE)
    LOOP
        IF CONVERT(empData.LAYOUT, 'US7ASCII') != empData.LAYOUT THEN
            DBMS_OUTPUT.PUT_LINE('Non ASCII found!');
        END IF;
    END LOOP;
END;
user906153
  • 1,218
  • 8
  • 30
  • 43