Results 1 to 4 of 4

Thread: charindex

  1. #1
    Join Date
    Feb 2004
    Posts
    64

    charindex

    I have following code in stored procedure. It was working before.

    declare @mail_cd varchar(3),
    @pointtype_id int, @mailcd varchar(3),@j int,@i int

    select @mail_cd = 'WT'
    select @i = charindex('WT',@mail_cd,1)

    select @mailcd =''

    select @i
    if @i = 1
    begin
    select @mailcd = substring(rtrim(@mail_cd),1,2)
    end
    else
    begin
    select @mailcd = substring(rtrim(@mail_cd),1,1)
    end

    select @mail_cd,@mailcd

    select @j = charindex('LF',@mail_cd,1)

    if @j = 1
    begin
    select @mailcd = substring(rtrim(@mail_cd),1,2)
    end
    else
    begin
    select @mailcd = substring(rtrim(@mail_cd),1,1)
    end

    select @pointtype_id = code_id
    from Code c,type ct
    where c.type_id = ct.type_id
    and ct.[name] = 'pointtype'
    and code = @mailcd

    select @mailcd,@pointtype_id


    I get result

    ---- -----------
    W 178

    (1 row(s) affected)

    instead of

    ---- -----------
    WT 278

    (1 row(s) affected)

    Any idea what is wrong? Thanks in advance

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,931
    If your query

    select @pointtype_id = code_id
    from Code c,type ct
    where c.type_id = ct.type_id
    and ct.[name] = 'pointtype'
    and code = @mailcd

    returns more than one row then the variable will have the data from last row.

  3. #3
    Join Date
    Feb 2004
    Posts
    64
    There is only one row. It is unique For ct.[name] = 'pointtype' and code = @mailcd.

    Thanks for reply.

  4. #4
    Join Date
    Sep 2005
    Posts
    168
    Code:
    .............
    select @mail_cd,@mailcd --@mail_cd = 'WT', @mailcd = 'WT'
    
    select @j = charindex('LF',@mail_cd,1)
    
    --at this point @j = 0, since 'LF' doesnt appear/exist in @mail_cd = 'WT'
    
    if @j = 1 --so this is skipped, since @j=0
    begin
    select @mailcd = substring(rtrim(@mail_cd),1,2)
    end
    else
    begin --and this step is valid/executed
    select @mailcd = substring(rtrim(@mail_cd),1,1) --@mailcd becomes 'W' 
    end
    --at this point @mailcd = 'W'
    ..............
    --HTH--

Posting Permissions

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