UDA

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-14-2009
01:17 AM

07-14-2009
01:17 AM

Hi,

I have a field in my table of data type decimal(7,4). While exporting to a file I want to format it in such a way that the output is :

input output

------ ----------

0.0000 .0

0.0320 .032

0.6654 .6654

0.1000 .1

99.5670 99.567

I have tried removing the extra zeroes by using trim(trim(both '0' from fld)). But this results in getting the value 0.0000 as '.'. I have also tried converting the decimal part as integer and appending with '.'.

This gives me the result but I want to know if any formatting can do it in an easy way.

Any sort of help on this is appreciated!

Thanks in Advance,

Chaitali

I have a field in my table of data type decimal(7,4). While exporting to a file I want to format it in such a way that the output is :

input output

------ ----------

0.0000 .0

0.0320 .032

0.6654 .6654

0.1000 .1

99.5670 99.567

I have tried removing the extra zeroes by using trim(trim(both '0' from fld)). But this results in getting the value 0.0000 as '.'. I have also tried converting the decimal part as integer and appending with '.'.

This gives me the result but I want to know if any formatting can do it in an easy way.

Any sort of help on this is appreciated!

Thanks in Advance,

Chaitali

11 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-14-2009
01:54 AM

07-14-2009
01:54 AM

Hello,

You can use anyone of the following:

- TRIM(LEADING '0' FROM fld) or

- TRIM(BOTH '0.' FROM fld) or

- Cast it to VARCHAR and then export, or

- Use FORMAT as follows:

SELECT CAST('0.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0010

SELECT CAST('99.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 99.0010

SELECT CAST('0.0000' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0000

HTH!

Regards,

Adeel

You can use anyone of the following:

- TRIM(LEADING '0' FROM fld) or

- TRIM(BOTH '0.' FROM fld) or

- Cast it to VARCHAR and then export, or

- Use FORMAT as follows:

SELECT CAST('0.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0010

SELECT CAST('99.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 99.0010

SELECT CAST('0.0000' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0000

HTH!

Regards,

Adeel

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-14-2009
07:08 AM

07-14-2009
07:08 AM

Thanks Adeel!

I have tried out the options other than formatting. But none of them helps me get my required output.

Also as per your formatting options :

SELECT CAST('0.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0010

SELECT CAST('99.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 99.0010

SELECT CAST('0.0000' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0000

My output format will be different. I need to get .0 if my input is 0.0000 or 0.02 if my input is 0.0200.

Please let me know if you are aware of any such formatting.

Thanks,

Chaitali

I have tried out the options other than formatting. But none of them helps me get my required output.

Also as per your formatting options :

SELECT CAST('0.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0010

SELECT CAST('99.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 99.0010

SELECT CAST('0.0000' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0000

My output format will be different. I need to get .0 if my input is 0.0000 or 0.02 if my input is 0.0200.

Please let me know if you are aware of any such formatting.

Thanks,

Chaitali

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-14-2009
07:14 AM

07-14-2009
07:14 AM

By the way .... why exactly do you need "0.123" to be ".123" .... ?

What you can do is, in your SELECT cast the formated value to VARCHAR and TRIM the LEADING "0"s. That should work.

Regards,

Adeel

What you can do is, in your SELECT cast the formated value to VARCHAR and TRIM the LEADING "0"s. That should work.

Regards,

Adeel

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-14-2009
07:38 AM

07-14-2009
07:38 AM

Hi Chaitali,

did you work with Oracle before? :-)

AFAIK there's no format for suppressing trailing zeros.

TRIM(BOTH '0' FROM col (FORMAT '9(3).9(4)')) || CASE WHEN col MOD 1 = 0 THEN '0' ELSE '' END

Dieter

did you work with Oracle before? :-)

AFAIK there's no format for suppressing trailing zeros.

TRIM(BOTH '0' FROM col (FORMAT '9(3).9(4)')) || CASE WHEN col MOD 1 = 0 THEN '0' ELSE '' END

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-14-2009
08:25 AM

07-14-2009
08:25 AM

Thanks Dieter!

Your solution works well! This is exactly what my output needs to be. But I was wondering if there is some formatting options which can do this as well.

Thanks a lot!

Chaitali

Your solution works well! This is exactly what my output needs to be. But I was wondering if there is some formatting options which can do this as well.

Thanks a lot!

Chaitali

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-14-2009
11:58 PM

07-14-2009
11:58 PM

Nice Dieter! :)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-23-2011
08:17 AM

03-23-2011
08:17 AM

Dieter's solution works great for me but I also have negative numbers and it changes the sign. How can I work around? Thanks.

-127.84000000 becomes 127.84

-127.84000000 becomes 127.84

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-23-2011
10:22 AM

03-23-2011
10:22 AM

What about -0.23000?

-.23:

TRIM(TRAILING '0' FROM col (FORMAT '-(4).9(8)')) || CASE WHEN col MOD 1 = 0 THEN '0' ELSE '' END

-0.23?

TRIM(TRAILING '0' FROM col (FORMAT '-(3)9.9(9)')) || CASE WHEN col MOD 1 = 0 THEN '0' ELSE '' END

Dieter

-.23:

TRIM(TRAILING '0' FROM col (FORMAT '-(4).9(8)')) || CASE WHEN col MOD 1 = 0 THEN '0' ELSE '' END

-0.23?

TRIM(TRAILING '0' FROM col (FORMAT '-(3)9.9(9)')) || CASE WHEN col MOD 1 = 0 THEN '0' ELSE '' END

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-05-2012
01:14 AM

04-05-2012
01:14 AM

Hi

I am facing an issue while loading data using Bteq.

The problem is while inserting data to table using bteq, the negative values are getting in as 0.

Please help here...

thnks

Rahul