Re: SQL character field to numeric and back

[CRPence <CRPbottle@xxxxxxxxx>]

Mark Allen wrote:
> Situation is I have a 13 byte alpha field in a vendor supplied table
> on the 400.
>
> The first 9 positions are a next number field as 000123456 followed
> by 4 blanks.
>
> We are putting a .NET app together that needs to update/increment
> the numeric portion of the data by 2 (in this example it would then
> be 000123458).. We've tried various combinations of CAST, CONVERT,
> SUBSTRING etc without any luck

  Given that is a thorough and accurate representation of the data in 
all rows for the field [i.e. valid representation of a numeric using 
character digits, sign, decimal separator, and only leading or trailing 
blanks], since I believe v5r3 which has implicit casting between numeric 
and character, then the following example shows all that should be 
required.  See the update:

  create table vfile (a13 char (13))
  ;
  insert into table vfile values('000123456    ')
  ;
  update vfile set a13=digits(decimal((a13+2), 9, 0)
  -- the parentheses around the addition are optional
  ;
  select count(*) from vfile where a13=12345678
  -- that should return one, for the one updated row

Regards, Chuck