Find out if a string contains only ASCII character

问题:

I need to know whether a string contains only ASCII characters. So far I use this REGEX:

DECLARE
    str VARCHAR2(100) := 'xyz';
BEGIN
    IF REGEXP_LIKE(str, '^[ -~]+$') THEN
        DBMS_OUTPUT.PUT_LINE('Pure ASCII');
    END IF;
END;
/

Pure ASCII

' ' and ~ are the first, resp. last character in ASCII.

Problem is, this REGEXP_LIKE fails on certain NLS-Settings:

ALTER SESSION SET NLS_SORT = 'GERMAN'; 

DECLARE
    str VARCHAR2(100) := 'xyz';
BEGIN
    IF REGEXP_LIKE(str, '^[ -~]+$') THEN
        DBMS_OUTPUT.PUT_LINE('Pure ASCII');
    END IF;
END;
/

ORA-12728: invalid range in regular expression
ORA-06512: at line 4

Do anybody knows a solution which works independently from current user NLS-Settings? Is this behavior on purpose or should it be considered as a bug?

回答1:

You can use TRANSLATE to do this. Basically, translate away all the ASCII printable characters (there aren't that many of them) and see what you have left.

Here is a query that does it:

WITH input ( p_string_to_test) AS ( 
SELECT 'This this string' FROM DUAL UNION ALL
SELECT 'Test this ' || CHR(7) || ' string too!' FROM DUAL UNION ALL
SELECT 'xxx' FROM DUAL)
SELECT p_string_to_test, 
       case when translate(p_string_to_test, 
       chr(0) || q'[ !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~]', 
       chr(0)) is null then 'Yes' else 'No' END is_ascii
FROM input;

+-------------------------+----------+
|    P_STRING_TO_TEST     | IS_ASCII |
+-------------------------+----------+
| This this string        | Yes      |
| Test this  string too!  | No       |
| xxx                     | Yes      |
+-------------------------+----------+

回答2:

ASCII function with upper limit of 127 may be used :

declare
    str nvarchar2(100) := '\xyz~*-=)(/&%+$#£>|"éß';
    a   nvarchar2(1);
    b   number := 0;
begin
    for i in 1..length(str)
    loop                 
      a := substrc(str,i,1);
      b := greatest(ascii(a),b);      
    end loop;

    if b < 128 then  
     dbms_output.put_line('String is composed of Pure ASCII characters');
    else
     dbms_output.put_line('String has non-ASCII characters');      
    end if; 
end;

回答3:

I think I will go for one of these two

IF CONVERT(str, 'US7ASCII') = str THEN
    DBMS_OUTPUT.PUT_LINE('Pure ASCII');
END IF;



IF ASCIISTR(REPLACE(str, '\', '/')) = REPLACE(str, '\', '/') THEN
    DBMS_OUTPUT.PUT_LINE('Pure ASCII');
END IF;