Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: SQL - string validation help

    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.

  2. #2
    Join Date
    Aug 2003
    Posts
    40
    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.

  3. #3
    Join Date
    Aug 2003
    Posts
    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(vStringLOOP
          vChar 
    := UPPER(SUBSTR(vString,vCount,1));    

          IF 
    NOT(ASCII(vCharBETWEEN 48 AND 57 OR ASCII(vCharBETWEEN 65 AND 90THEN
            
    RETURN(FALSE);
          
    END IF;
          
    vCount := vCount+1;
        
    END LOOP;
      
    END IF;
      RETURN(
    TRUE);
    END alphaNumOnly;


  4. #4
    Join Date
    Dec 2003
    Posts
    2
    I had to convert the code to SQL, and it works as a SQL function.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •