Hi all,

I want to convert a character field to a numeric field using SQL400 in
an AS400 file.

Is this possible?

Thanks in advance.

group...@hotmail.com wrote:
> Hi all,
>
> I want to convert a character field to a numeric field using SQL400
in
> an AS400 file.
>
> Is this possible?
>
> Thanks in advance.

It is possible, but (rather obviously), if your character field
contains some data that won't translate to numeric, your process will
choke.

Let's say you have a 5-byte character field 'char_num' that you KNOW
for certain only contains valid 5-digit numeric values, and you want to
convert them dynamically to a decimal representation.  Then you could
code

   select dec(char_num) from mytable

Alternatively, you can code

   select cast(char_num as dec(5,0)) from mytable


It's worth repeating:  if your field contains non-numeric values (other
than spaces), the conversion will fail.

Thanks Jonathan,

This char field has some negative signs in it so that must be why I
can't get the CAST function to work.

groupiis@hotmail.com wrote:
> Thanks Jonathan,
> 
> This char field has some negative signs in it so that must be why I
> can't get the CAST function to work.
> 

Not impossible to handle, especially if the sign is
always in the same position.  The code will be a little
junky, though.  Assume a 7-character field with either
7-digit positive numbers or 6-digit negative numbers
with a trailing sign.  Then you can code:

    select case substr(char_num,7,1)
             when '-'
               then cast(left(char_num,6) as dec(7,0)) * -1
             else
               cast(char_num as dec(7,0))
           end
      from mytable
     where ...

If the position of the negative sign is variable, e.g.
'123456-', '123-   ','8-     ', then you can use the
POSSTR function to determine where it is in the field:

    select
      case
        when char_num like '%-%'
          then cast(left(char_num,posstr(char_num,'-') 
- 1) as dec(7,0)) * -1
        else cast(char_num as dec(7,0))
      from mytable
     where ...