db2-- Decimal to Varchar without leading zeros - Page 3

DB2Plus · #31 (**permalink**) 08-11-09, 06:35

Quote:

Originally Posted by Peter.Vanroose

Indeed, at least, for strictly positive integers.
For the number 0, it's giving an unwanted empty string...

See the message from Lenny77. It's working without any problem.

K.S

livefaq · #32 (**permalink**) 04-08-10, 09:16

I am surffing for a while in internet to solve decimal conversion problem finally i found a website which is very effective
to solve decimal to fraction conversion problem.. i am sharing you guys my discovery here is a URL of the website Decimal to fraction,Deciamls to fractions,decimal to fractions

Peter.Vanroose · #33 (**permalink**) 04-08-10, 10:16

Actually, that's the well-known continued fraction expansion algorithm. (I looked into this for my Master thesis, very long ago ;-)

But that was not the original question of this thread, I believe...

sandydaufen · #34 (**permalink**) 11-25-10, 14:41

thanks for the work around, might be useful in the future

MatthewNeill · #35 (**permalink**) 01-04-11, 18:26

An IfNull NullIf combination can make the Trim Leading '0' example workable even for a true '0' only value.

Code:


VarChar(
   IfNull(
      NullIf(
         Trim(Leading '0' From 
            Trim(
               Char( -000010 )
            )
         )
      ,'')
   ,'0')
)

Will result in '-10'

Just replace -000010 with your numeric column...

Regards...

Lenny77 · #36 (**permalink**) 01-04-11, 19:06

You are almost create the nuclear bomb from the scratch:

Code:

select varchar(int('-000010'))
from sysibm.sysdummy1
;

Lenny

MatthewNeill · #37 (**permalink**) 01-04-11, 19:19

The heading of this thread is "Decimal to Varchar without leading zeros" so I assume that there may be digits to the right of the decimal point that would want to be returned...

Code:

select varchar(int('-010.10'))
from sysibm/sysdummy1

yeilds '-10' instead of '-10.10',

whereas my bomb:

Code:

Select 
VarChar(
   IfNull(
      NullIf(
         Trim(Leading '0' From 
            Trim(
               Char( -010.10 )
            )
         )
      ,'')
   ,'0')
)
from sysibm/sysdummy1

has a (10.10 megaton) yeild of '-10.10' like it should...

Lenny77 · #38 (**permalink**) 01-05-11, 12:45

Believe me, I like your query very much, and I am understanding how it works, but people like something easier looking.

For example:

Code:

select    strip(strip(left(decChar, 1), L, '0'), L, '+') 
       || strip(strip(substr(decChar, 2)), L, '0') chardec
from (
Select strip('   -  000077.77') as decChar
from sysibm.sysdummy1 ) t1

Returns:

Quote:

CHARDEC
-77.77

In my opinion, even beginner DB2 programmer can create something like this query....

Lenny

Lenny77 · #39 (**permalink**) 01-05-11, 13:13

Quote:

Originally Posted by MatthewNeill

An IfNull NullIf combination can make the Trim Leading '0' example workable even for a true '0' only value.

Code:


VarChar(
   IfNull(
      NullIf(
         Trim(Leading '0' From 
            Trim(
               Char( -000010 )
            )
         )
      ,'')
   ,'0')
)

Will result in '-10'

Just replace -000010 with your numeric column...

Regards...

I just copy your query and run:

Code:

select VarChar(
   IfNull(
      NullIf(
         Trim(Leading '0' From 
            Trim(
               Char( -000010 )
            )
         )
      ,'')
   ,'0')
)
from sysibm.sysdummy1

And what I have instead of result:

SQL0104N An unexpected token "'0'" was found following "". Expected tokens may include: "CONCAT || / MICROSECONDS MICROSECOND SECONDS SECOND MINUTES". SQLSTATE=42601

State:42601,Native:-104,Origin:[IBM][CLI Driver][DB2]

Please correct your query and publish again.

Your BOMB was not explode

Lenny

MatthewNeill · #40 (**permalink**) 01-05-11, 13:28

That is so funny, because the only reason I posted the "Trim Leading Zero" reimplementation with the IfNull-NullIf was because if thought that it WAS easier to understand then the case-cast-abs-strip-cast-substr-cast... implementation...

How about a compromise... Your CASE for my NULLS and my TRIM LEADING for your STRIPS and SUBSTR...

Code:

Select 

case when dec_val = 0 then '0'
else Trim(Leading '0' From Char( dec_val ))
end as zero_suppressed_decimal_string

from (select -010.10 dec_val from sysibm/sysdummy1) as t1

Code by Committee~!

Cheers!

Lenny77 · #41 (**permalink**) 01-05-11, 13:33

what DB2 system are you using ?
where sysibm/sysdummy1 is using instead of sysibm.sysdummy1 ?

MatthewNeill · #42 (**permalink**) 01-05-11, 14:27

Sorry I should have change the slash notation back to dot notation...

DB2 for i (the former DB2/400) is the third major incarnation of DB2. It is very closely incorporated into the operating system of the IBM System i machines...

A.K.A., "IBM Power Systems"...
A.A.K.A., "i5"...
A.A.A.K.A., "iSeries"...
A.A.A.A.K.A., "AS/400"...
A.A.A.A.A.K.A., Silverlake...

Matthew...

dorje · #43 (**permalink**) 06-23-11, 13:58

Well, maybe there are simpler ways, but I wrote this, and seems to work well...

RTRIM(char(integer(<DOUBLE_VALUE>))) ||'.'||right('00000'||trim(char(integer(round((abs(<DOUBLE_VALUE>)-trunc(abs(<DOUBLE_VALUE>),0))*10000,0)))),4)

The underlined values might needed to change if 4 decimal digits is not the choice you need...

Take care!
István

Internet Services

Web Development

Digital Marketing

Consumer Tech