don''t want null in the field.

• bluepaperbag

  SSCrazy

  Points: 2055

  More actions

  September 21, 2006 at 11:32 am

  #174160

  create table [TempAddressParsingTable](

  id_voter int null,

  id_town varchar(10) null,

  full_address varchar(100) null,

  ad_num int null,

  ad_str1 varchar(50) null,

  full_ad_mail varchar(100) null,

  ad_mail_str1  varchar(50) null,

  ad_mail_str2  varchar(50) null,

  ad_num_suffix_a varchar (10) null,

  ad_num_suffix_b varchar (10) null,

  ad_unit varchar(10) null)

  truncate table tempAddressparsingTable

  insert TempAddressParsingTable (id_voter,ad_num,ad_str1)

  select '1', null, '102 OLD COUNTY ROAD' union all

  select '2','30', 'SHAW ROAD'

   

  update TempAddressParsingTable

   

  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)

   and ad_num is null

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

   -- ELSE null

   END

  select * from TempAddressParsingTable

  how can I keep '30' in the ad_num field after executing my update statement. somehow my update statement changes it to null.?

   

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 21, 2006 at 11:41 am

  #661824

  If you want to updated the records that have Null in ad_num then modify your query to exclude Not Null ad Column.

  But by modifying your query slightly will give the result that u arer looking for.

   

  update TempAddressParsingTable

   

  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)

   and ad_num is null

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

   -- ELSE null

   END

  where ad_num is null

  Thanks

  Sreejith