SQL - string validation help

PlzHelpMe · #1 (**permalink**) 12-19-03, 11:42

I have a column cID defined as varchar(20). I need to validate the content to be only numbers (0-9) and/or alphabets (Aa-Zz). It cannot have nonalpha characters like !:\.+=_ etc...

For validating numbers I can simply use ISNUMERIC(). But is there any way to validate for alpha without having to create a loop to look at each position?

TIA.

olerag · #2 (**permalink**) 12-19-03, 12:06

Iterate thru the string is the best way.

1. Force the test string to upper-case.
2. Iterate thru string.
3. Check for chars with ascii char codes between
48-57 (for numbers) AND 65-90 (for chars).
3. Any char outside the realm of #2, above, fails.

Would you like a code example??? You can make it a
public function, stored in the database, and re-use it
whenever you like. It'd probably be between 10-20 lines.

olerag · #3 (**permalink**) 12-19-03, 12:40

Here's your code....Note that NULLs return a boolean success (true).
If you don't like it, the code can be altered as you need.

PHP Code:


			
CREATE OR REPLACE FUNCTION alphaNumOnly(vString IN VARCHAR2) RETURN BOOLEAN IS

  vCount NUMBER := 1;

  vChar  VARCHAR2(1);

BEGIN

  IF vString IS NOT NULL THEN

    FOR i IN 1..LENGTH(vString) LOOP

      vChar := UPPER(SUBSTR(vString,vCount,1));    



      IF NOT(ASCII(vChar) BETWEEN 48 AND 57 OR ASCII(vChar) BETWEEN 65 AND 90) THEN

        RETURN(FALSE);

      END IF;

      vCount := vCount+1;

    END LOOP;

  END IF;

  RETURN(TRUE);

END alphaNumOnly;

/

PlzHelpMe · #4 (**permalink**) 12-19-03, 13:13

I had to convert the code to SQL, and it works as a SQL function.

Thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech