Home

How to tell whether a character is a letter or not

Thu, 2006.09.28 - 12:21 — müzso
Let's say you have a variable with a character in it and you want to know whether it is a letter or not. First of all choose a proper value for your language for use in the NLS_SORT parameter ('XHUNGARIAN' in my example).
Then use the NLSSORT function to compare the char to the ABC of your language:
set serveroutput on
DECLARE
  nlssortby CONSTANT VARCHAR2(100) := 'NLS_SORT=XHUNGARIAN';
  abc_first CONSTANT RAW(10) := NLSSORT('A', nlssortby);
  abc_last CONSTANT RAW(10) := NLSSORT('z', nlssortby);
  my_char VARCHAR2(10) := 'é';
BEGIN
  IF NLSSORT(my_char, nlssortby) BETWEEN abc_first AND abc_last THEN
    dbms_output.put_line('The character "' || my_char || '" is a letter.');
  ELSE
    dbms_output.put_line('The character "' || my_char || '" is not a letter.');
  END IF;
END;
/

Note in my example that the first letter of the Hungarian ABC in the XHUNGARIAN sorting is a capital 'A' and the last letter is a lower 'z'!

If you simply compare the character with the 'A' and 'z' letters, then the result will depend on the current settings of the NLS_SORT and NLS_COMP initialization parameters.