MVSFORUMS.com

Anta · Beginner Joined: 10 Oct 2006 Posts: 12 Topics: 5

Hi,
can some one help me please?
Maybe the question is silly and dummy, but I have stopped with it.
I need to select some values from tables without leading chars, for ex, without leading zeroes.
But it looks like STRIP function doesn't work for it:
select strip(char(decimal_value),L,'0') from some.table
I mean I can remove trailing zeroes having specified T or B argument,
but I can do nothing with leading zeroes.
Maybe I do something wrong.
Can you please help with it?

Thanks in advance,
Anta

karthikeyan

Anta,
I tried executing the same Query that you have used it but it works well for me. May I know the Error that you are facing while executing this Query??

Thanks
Karthik

Anta · Beginner Joined: 10 Oct 2006 Posts: 12 Topics: 5

Karthik,

thank you for your reply.
No, it doesn't work for me.

If I run
select char(decimal_value)
from some.table
I receive:
0000000012.000000

When I run
select strip(char(decimal_value),L,'0')
from some.table
I receive the same:
0000000012.000000

But for T (trailing) the picture is changed:
select strip(char(decimal_value),T,'0')
from some.table
0000000012.

I expected that L-operand allows to remove the leading zeroes
but failed with it.

Anta

dbzTHEdinosauer · Posted: Wed Nov 08, 2006 7:39 am Post subject:

what is the pic definition for the host variable receiving the data from DB2?
_________________
Dick Brenholtz
American living in Varel, Germany

kolusu · Posted: Wed Nov 08, 2006 8:32 am Post subject:

Anta,

The reason your strip did not work is because, the CHAR scalar functions puts a blank in the first character.If the column is negative, the first character of the result is a minus sign. Otherwise, the first character is a blank,which means that a positive value always has one leading blank. So when you are using the strip , the leading character is not zero. So it does not remove the leading zeroes. In order to remove the leading zeroes you need to use the CAST function which does not return the leading blank.

Try this

Anta · Beginner Joined: 10 Oct 2006 Posts: 12 Topics: 5

Dick,

I unload the table using sql and form.
In the form this column is defined as char(16).
But even in qmf I can only remove trailing chars, not leading chars - I mean using strip function.

Anta

Anta · Beginner Joined: 10 Oct 2006 Posts: 12 Topics: 5

Kolusu,

thank you a lot!
It is what I need.
Thank you!

With kind regards,
Anta