Oracle PLSQL: Check an alphanumeric value in a string

Check an alphanumeric value in a string

Question: In Oracle, I want to know if a string value contains alphanumeric characters only. How can I do this?

Answer: To test a string for alphanumeric characters, you could use a combination of the LENGTH function, TRIM function, and TRANSLATE function built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' ')))

string1

The string value that you are testing.

This function will return a null value if string1 is alphanumeric. It will return a value "greater than 0" if string1 contains any non-alphanumeric characters.

For example,

LENGTH(TRIM(TRANSLATE('Tech on the Net!!!', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' ')))
Result: 3

LENGTH(TRIM(TRANSLATE('Tech1Net2^', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' ')))
Result: 1

LENGTH(TRIM(TRANSLATE('Tech on the Net567', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ+-.0123456789', ' ')))
Result: null

SQL> SET SERVEROUTPUT ON

SQL> 

SQL> -----THIS PROGRAM CHECKS SPECIAL CHARACTERS VALIDTY IN A STRING

SQL> DECLARE

  2   STRING_VALUE   VARCHAR2(4000):= '&INPUT_VALUE';

  3   CHK_VALUE     NUMBER:=0;

  4  BEGIN

  5     CHK_VALUE := NVL(LENGTH(TRIM(TRANSLATE(STRING_VALUE, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ' '))),0);

  6  

  7    IF CHK_VALUE = 0 THEN

  8      DBMS_OUTPUT.put_line('FOUND ONLY ALPHA NUMERIC'||CHR(10)||STRING_VALUE);

  9    ELSE

 10      DBMS_OUTPUT.put_line('FOUND SPECIAL CHARACTERS ALSO'||CHR(10)||STRING_VALUE);

 11    END IF;

 12  

 13  END;

 14  /

 

FOUND ONLY ALPHA NUMERIC

ASDFASDF123

 

PL/SQL procedure successfully completed

 

SQL> /

 

FOUND SPECIAL CHARACTERS ALSO

ASDFASDF@123

 

PL/SQL procedure successfully completed