sql server if statement

Question

I do have this situation I am trying to use if else for the following .It does work but it doesnt work as expected..when there is nothing choosen for the preState it will bring Null I want it to display for each choices.Could you please have a look at it.Thanks guys..something is wrong with my if...."you can copy and run it it"

       DECLARE     @returnvalue VARCHAR(50)
        ,@state VARCHAR(50)
        ,@i INT
        ,@postState  VARCHAR(50)
        ,@preState  VARCHAR(50)
        ,@Anystring  VARCHAR(255)
        ,@state1 VARCHAR(50)
        ,@state2 VARCHAR(50)

        SET @Anystring='IA all the states Colorado '

      SELECT @state =LTRIM((RTRIM(SUBSTRING(@Anystring, LEN(@Anystring) -      CHARINDEX(' ', REVERSE(@Anystring))+1 , LEN(@Anystring)))))

         SET @state =REPLACE(REPLACE(@Anystring, '.', ' '), '&', ' ')

         set @state1=@state


  SET @i = CHARINDEX(' ',@state1 )

  If (@i > 0 and SUBSTRING(@state1, 1, @i-1) IN ('IA','CO','Iowa','Colorado'))
     begin
     SELECT  @preState = RTRIM(SUBSTRING(@state1, 1, 1)), 
             @state1 = LTRIM(SUBSTRING(@state1, @i+1, 999))
     end

   else IF (@i > 0 and SUBSTRING(@state, 1, @i-1) IN ('DC','SEA','NY'))
     begin
     SELECT  @preState = RTRIM(SUBSTRING(@state1, 1, 2)), 
             @state1 = LTRIM(SUBSTRING(@state1, @i+1, 999))

     end        


   SET @i = LEN(@state1) - CHARINDEX(' ', REVERSE(rtrim(@state1)))
  IF (@i > 0 and @i < LEN(@Anystring) and SUBSTRING(@state1, @i+2, 999) IN     ('IA','CO','Iowa','Colorado')) 
      begin
     SELECT @postState = SUBSTRING(@state1, @i+2, 1),
             @state1 = RTRIM(SUBSTRING(@state1, 1, @i))
      end       

else IF (@i > 0 and @i < len(@state)  and SUBSTRING(@state1, @i+2, 999) IN ('DC','SEA','NY')) 
    begin
     SELECT @postState = SUBSTRING(@state1, @i+2, 2),
             @state1 = RTRIM(SUBSTRING(@state1, 1, @i))
    end         

else IF (@i > 0 and SUBSTRING(@state1, 1, @i-1) IN ('','  ','   '))  
        begin

        SELECT @postState = '',
             @state1 = LTRIM(SUBSTRING(@state1, 1, 999))          
        end           
      SET  @state1=@preState+' ' + @state1+' '+@postState 

      set @state2=@state1


      select @state2

Welcome to Stack Overflow! Please be aware that tags can't be combined to create words. That is, tagging with sql and server does not mean you're talking about Microsoft SQL Server. — Charles
– Charles, Commented Oct 4, 2012 at 23:14

RichardTheKiwi · Accepted Answer · 2012-10-04 23:26:02Z

1

If you mean the you get NULL from

SET  @state1=@preState+' ' + @state1+' '+@postState

Then you will want to ISNULL it to handle the lack of prefix

SET  @state1= isnull(@preState+' ','') + @state1+' '+@postState

Same for @poststate

answered Oct 4, 2012 at 23:26

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mntne Dav Over a year ago

@RichardTheKiwi..Thank you so much for taking the time and resolving the issue.It works Great..I appreciate it..Thanks all

Collectives™ on Stack Overflow

sql server if statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related