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.
email@example.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 ...