show street number

• bluepaperbag

  SSCrazy

  Points: 2055

  More actions

  September 27, 2006 at 1:57 pm

  #153204

  declare @table table(ad_num varchar(7), ad_str1 varchar(50))

  insert @table (ad_num, ad_str1) values('23', 'apple road')

  insert @table (ad_num, ad_str1) values(null, '7 apple road')

  select * from @table

  update @table

     

    set ad_num =

    case

    WHEN (CHARINDEX(' ', ad_str1) < 10) AND (CHARINDEX(' ', ad_str1) <> 0)

      --creates a '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' mask to compare against

    AND LEFT(ad_str1, CHARINDEX(' ', ad_str1)-1) LIKE REPLICATE('[0-9]', CHARINDEX(' ', ad_str1)-1)

    

    THEN substring(ad_str1,1,charindex(' ',ad_str1))

    else null

    end

    select * from @table

   

   

  im writing this code to update the field ad_num. Once is updated, the other record turns into null. How can i keep the record value.

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 27, 2006 at 2:06 pm

  #662853

  Change Null to ad_num in Case statement like this

  update @table

    

    set ad_num =

    case

    WHEN (CHARINDEX(' ', ad_str1) < 10) AND (CHARINDEX(' ', ad_str1) <> 0)

      --creates a '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' mask to compare against

    AND LEFT(ad_str1, CHARINDEX(' ', ad_str1)-1) LIKE REPLICATE('[0-9]', CHARINDEX(' ', ad_str1)-1)

    THEN substring(ad_str1,1,charindex(' ',ad_str1))

   else

   ad_num

    end

    select * from @table

  

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  September 27, 2006 at 4:12 pm

  #662878

  WHEN (CHARINDEX(' ', ad_str1) < 10) AND (CHARINDEX(' ', ad_str1) <> 0

  I'm wondering what must happen to give you CHARINDEX () < 0 ?

  

  _____________
  Code for TallyGenerator