format decimal to char

Posted on 2007-10-01
Medium Priority
Last Modified: 2008-03-22
hi all,

i am using  the below query to do a fast export in teradata

CAST((b.id  (FORMAT 'zzzzzzzzzzzzv')) AS CHAR(12)),CAST('|'  as char(1)),
customer b

here the column id in table has both negative and postive values but the above sql changes all negative numbers to postive. can anyone suggest me how to retain the negative number when i do select ?
sample customer table data

Question by:rajeshnayakwadi
  • 2
LVL 19

Expert Comment

ID: 19996977
Sign characters.
May be placed at the beginning or end of a format string. One sign
character places the edit character in a fixed position for the output
If two or more of these characters are present on the left, the sign floats
(moves to the position just to the left of the number as determined by the
stated structure).
The + translates to + or - as appropriate; the - translates to - or blank.

Author Comment

ID: 19998580
Hi Nick,
can you please explain with an example how to preserve the sign ?
LVL 19

Accepted Solution

NickUpson earned 375 total points
ID: 19999769
depends if you want positive numbers to have a + sign, assuming you don't

CAST((b.id  (FORMAT '-zzzzzzzzzzzzv')) AS CHAR(12)),CAST('|'  as char(1)),
customer b

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question