Hmm, I think char() will mess you up if you have leading zeros... Use DIGITS() instead...

set substr(field, 1, 9) = digits(decimal(substr(field, 1, 9), 9, 0) + 2)


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Christen, Duane J.
Sent: Thursday, July 10, 2008 4:00 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL character field to numeric and back

Something like this:

set substr(field, 1, 9) = char(decimal(substr(field, 1, 9), 9, 0) + 2)

Duane Christen

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Mark Allen
Sent: Thursday, July 10, 2008 3:16 PM
To: Midrange Systems Technical Discussion
Subject: SQL character field to numeric and back

Situation is I have a 13 byte alpha field in a vendor supplied table on the

The first 9 positions are a next number field as 000123456 followed by 4

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

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives

This email has been scanned by the MessageLabs Email Security System.
For more information please visit

NOTICE: This electronic mail transmission may contain confidential information and is intended only for the person(s) named. Any use, copying, or disclosure by any other person is strictly prohibited. If you have received this transmission in error, please notify the sender via e-mail.

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact