I am trying to cast a Decimal datatype to Varchar using
SELECT cast(char (OUTSTANDINGBAL) as varchar (30))
FROM CREDITTable;

and i get the results as

000000000000012.250

Decimal data type is defined as decimal(18,3) on the CREDITTable.

I need to avoid the leading zeros and get the 12.250 to insert into anothe table.

Is there any way I can do this?
I looked through most of the functions db2 has but it didn't get me anywhere.

I would really apprecaite it if you could forward any examples...

thanks
faris

One of the ways is char and rtrim combo
values rtrim( char ( 1.2345 ) )

You need rtrim to remove trailing spaces

Well.. you can't use rtrim on decimal... so that won't work. when I tranfer Decimal to character it will have the leading zeros and does not have trainling spaces to remove.. so i end up with leading zeros..

Probably I do not understand something, but I said you should use 2 functions char AND rtrim
First will convert to character with possible trailing spaces, the second will remove them

regards,
dmitri

Hi fhussain,

how about something along these lines:

select

substr(cast( 1000*OUTSTANDINGBAL as bigint ) , 1, length( cast( 1000*OUTSTANDINGBAL as bigint ) -3) || ',' ||
substr(cast( 1000*OUTSTANDINGBAL as bigint ) , length( cast( 1000*OUTSTANDINGBAL as bigint ) -2, 3)

from CREDITTable

Didn't check for syntax, but you may get the idea anyway.

Johann

When you convert it to char it will have leading zeros infront of the decimal. So, using the trim will not remove those zeros cos it wud not have any trailing space. when you convert decimall to character you will not get trailing spaces instead you will get zeros in form of the number.

Fora example say you specify deciaml (18,3) and hold a value of 12.5.
when you change this to char you will have 0000012.500 somthing like that.
So, i need to emove those leading zeros.

Hi
You can use the following expression to remove the leading/trailing spaces resulting from decimal to varchar conversion.

replace(replace(rtrim(ltrim(replace(char(<FIELD_NAME>), '0', ' '))), ' ', '0'), '.', '')

Regards
Ankur Jain

http://ankurjain.org

how about:

STRIP ( CHAR ( column ) , L , '0' )

?

I'd suggest not to use REPLACE(...), as any '0' would be replaced by blanc ( not only the leading or trailing ones ).



*ups* I just saw that the original post is several years old ...

Actually, even the STRIP function doesn't work on certain OSes. Same reason goes for using RTRIM & LTRIM functions, instead of TRIM function.

You're right that REPLACE will remove all 0s in the variable, but if you read the code completely, you'll see I'm repopulating the blank fields again with zero.

I think ankursmart is correct. First replace banks with 0, then ltrim and rtrim spaces and final replace reminding (non-leading) spaces to 0.

I had similar problem few months ago. Beside removing leading 0 I had to align text to the right site and the final string had to be exactly 10 characters long.

Sample (before):
Sample (after):
If I remember correctly beside replace, ltrim, rtrim, replace functions I have also used length, case and space functions.

Hope this helps,
Grofaty

the solution
fails for negative numbers and numbers with digits after the decimal point.

Wim, the expression will remove all the leading/trailing zeroes & decimals from the field; if we don't want that we can use only ltrim & remove the decimal replace part i.e.

replace(ltrim(replace(char(<FIELD_NAME), '0', ' ')), ' ', '0')

It basically depends upon requirements. Leading zeroes may be removed, comma & decimal characters wont be removed.

Thanks

For example:

MY_VALUE DECIMAL(9,0)

SELECT CHAR(MY_VALUE)
FROM MY_TABLE

000100045.
100003567.
010555365.


SELECT VARCHAR(MY_VALUE)
FROM MY_TABLE

100045.
100003567.
10555365.

So if you want to trim the leading zeros from your string then use VARCHAR().
If you want to leave the leading zeros (as we do since we are processing SSNs) then use CHAR().

--
Dan Glauser
J2EE Architect

The following does exactly what you want:
(At least, on DB2 v8 for z/OS; don't know about other versions.)

Here is a sample workaround. You need to know the precision and scale value of FIELD_NAME.

select CHAR(RTRIM(char(integer(FIELD_NAME))) ||'.'||char(substr(char(decimal(FIELD_NAME,17,2)), 17))) from TABLE_NAME where row_id ='xxxxxx'

Hope this helps ,
-Nitya