locked
While loop in a function RRS feed

  • Question

  • I have this function working until I add a OR condition to take care the null input.
    CREATE function  
     DBO.IsAlpha (  @sInput CHAR ( 50 ) )  
    RETURNS INTEGER  
    BEGIN  
     
      DECLARE @nCounter INTEGER;  
      DECLARE @nMaxLength INTEGER;  
      DECLARE @nASC INTEGER;  
      SELECT @nCounter=1     
      SELECT @nMaxLength=LEN(@sInput)   
     
      WHILE  (@nCounter <= @nMaxLength)  --OR (@sInput IS  NULL)  
        begin  
         SELECT @nASC=ASCII(SUBSTRING(@sInput,@nCounter,1))    
            IF (@nASC > 90 AND @nASC < 97) OR  
             @nASC > 122 OR (@nASC < 65 AND @nASC <> 32)   
             begin  
              RETURN( 0 )  
            END    
           SELECT @nCounter=@nCounter+1         
       end  
      RETURN (1)  
    END  
    GO 

    Now if I execute

    SELECT dbo.ISALPHA('99') returns 0, that is right
    Select dbo.isalpha('A') returns 1 , that is right.
    Select dbo.isalpha('') returns 1, that is not right.

    Any help.  May be it is a long day for me, my brain is not working right at this time.

    TIA

    Wednesday, February 25, 2009 11:00 PM

Answers

  • Change the last return statement by:


    IF
    @nCounter > 1
        
    RETURN (1);

    RETURN (0);


    or try:

    CREATE FUNCTION dbo.IsAlpha2 (  @sInput CHAR ( 50 ) )     
    RETURNS INTEGER     
    BEGIN     
    RETURN (  
    CASE  
    WHEN @sInput = '' OR @sInput IS NULL THEN 0  
    WHEN @sInput LIKE '%[^a-zA-Z ]%' THEN 0  
    ELSE 1  
    END  
    )  
    END;     
    GO    
     


    AMB

    • Marked as answer by Yum64147 Thursday, February 26, 2009 3:09 AM
    Thursday, February 26, 2009 2:56 AM

All replies

  • Change the last return statement by:


    IF
    @nCounter > 1
        
    RETURN (1);

    RETURN (0);


    or try:

    CREATE FUNCTION dbo.IsAlpha2 (  @sInput CHAR ( 50 ) )     
    RETURNS INTEGER     
    BEGIN     
    RETURN (  
    CASE  
    WHEN @sInput = '' OR @sInput IS NULL THEN 0  
    WHEN @sInput LIKE '%[^a-zA-Z ]%' THEN 0  
    ELSE 1  
    END  
    )  
    END;     
    GO    
     


    AMB

    • Marked as answer by Yum64147 Thursday, February 26, 2009 3:09 AM
    Thursday, February 26, 2009 2:56 AM
  • Thanks. I love the second solution.
    Thursday, February 26, 2009 3:10 AM