Char to decimal 1

[Pekka]

Hi,
does anyone know how to convert character field to numeric in db2 udb. My problem is the thousand separator period(.) I can use translate function to get rid off the period but it doesn't help much.

 

[gregsimpson]

Pekka,

use the DEC function, when the input type is char the decimal point format can be specified.

I believe the following link contains enough info for you.

http://www.ibm.com/ru/software/download/db2v61cook.pdf

Cheers
Greg

 

[Pekka]

Hi Greg,
yep, I have tried dec function but maybe I haven't found the proper syntax. The char value is:'10.233,50'. and when I use dec function I get error. Maybe I should first split the value from left and right of the period and then concat them again and then use dec to get it decimal?

 

[blom0344]

Try something along these lines:

Select FIELD,DEC(REPLACE(FIELD,'.',''),7,2,',') from ..............


T. Blom
Information analyst
tbl@shimano-eu.com

 

[Pekka]

T.Blom,
I'm Db2 udb for iSeries V5R2M0 and it doesn't support replace function and if I use translate it left space in thousand separator place.

 

[blom0344]

If this is for one value, then why not concatenate 2 substrings (making sure you get rid of the '.') and converting the resulting string with the DEC function...?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[zeat]

Hi Pekka,

it's a little long winded, but you could try the following (assuming in this case your value is in a field called CHAR_10):

SELECT DEC(STRIP(TRANSLATE(SUBSTR(CHAR_10,1,2),' ','.'),B)
|| STRIP(TRANSLATE(SUBSTR(CHAR_10,3,2),' ','.'),B)
|| STRIP(TRANSLATE(SUBSTR(CHAR_10,5,2),' ','.'),B)
|| STRIP(TRANSLATE(SUBSTR(CHAR_10,7,2),' ','.'),B)
|| STRIP(TRANSLATE(SUBSTR(CHAR_10,9,2),' ','.'),B),10,2)


This takes the string a pair of characters at a time, converts '.' to a space, and then trims off any spaces at either end, concatenates them all together and finally converts to a decimal (10,2)

Hope this helps
Zeat

 

[Pekka]

Beautiful solution Zeat! It worked like a charm!