Solved

Formatting SQL output

Posted on 2013-06-25
15
286 Views
Last Modified: 2013-06-25
Trying to produce output query results but not having any luck.  Need to take a summed field and produce string output.  Tried cast(field AS Varchar(5)) but it doesn't produce what I am looking for.  Is there another way to format output from a query?
0
Comment
Question by:No1Coder
  • 4
  • 4
  • 4
  • +1
15 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 39274547
Hi No1Coder
Can you give an example of what you want?
What format is the input and what format do you want to output it?

Some samples:
DECLARE @v MONEY
SELECT @v = 1322323.6666

SELECT CONVERT(VARCHAR,@v,0)  --1322323.67     
Rounded but no formatting

SELECT CONVERT(VARCHAR,@v,1)    --1,322,323.67     
Formatted with commas

SELECT CONVERT(VARCHAR,@v,2)    --1322323.6666  
No formatting

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39274556
well, yes, but more details of your requirement are needed for a better answer.

what exactly are you looking for?

e.g.
what is un-converted summed field (integer? fixed decimal? money? float?)
and what is the expected result? (currency symbol? rounded? truncated? leading zeros?)
0
 

Author Comment

by:No1Coder
ID: 39274587
The output needs to be five characters, with two decimal places, and a leading sign (either ' ' or '-').  No commas or decimal points.  Is it is a decimal (5,2) field.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39274612
2 decimal places with no decimal indicator

so
12345.67

would become

+1234567
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39274623
declare @n decimal (5,2)
set @n = 123.45

select 
 case when @n > 0 then '+' + replace(convert(varchar(6),@n),'.','')
      else             '-' + replace(convert(varchar(6),@n),'.','')
 end

Open in new window

0
 
LVL 11

Expert Comment

by:Louis01
ID: 39274628
declare @v decimal(5,2) = 1234.56;
select case when @v >= 0 then ' ' else '-' end + convert(varchar, @v)

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39274677
in case you are wondering why varchar(6) it's because of the decimal symbol, replace removes it (hence a maximum of 5, but then you add the positive/negative symbol, so the result can be 6 chars. hope that makes sense.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

Expert Comment

by:Zberteoc
ID: 39274790
If the type or your value is decimal(5.,2) then you need at least a length of 7 to turn it into a varchar:

declare @v decimal (5,2)=-123.45

select cast(@v as varchar(7)) as v_neg, cast(-1*@v as varchar(7)) as v_pos

-- result:
v_neg   v_pos
------- -------
-123.45 123.45

Open in new window

3 integers + 2 decimals + decimal sign(.) + number sign(- if negative)
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39274819
Of course if you want to remove the - and/or . then you simply use replace. You don't need any test:

declare @v decimal (5,2)=-123.45

select left(replace(replace(cast(@v as varchar(7)),'-',''),'.',''),5) as v

-- result:
v
-----
12345

Open in new window

I used LEFT(.., 5) just to limit the number of --- in the text result.
0
 

Author Comment

by:No1Coder
ID: 39275597
I need the output to occupy 5 characters (varchar(5) for example).  Can I control space or zero fill?

for example, if the field contains 1.23, I need the output to contain either "  123" or " 0123".  The leading space indicates positive.  The first example has two leading spaces.
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39275668
declare @v decimal(5,2) = 1.23;
select case when @v >= 0 then ' ' else '-' end + convert(char(4), @v * 100);
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275696
Then you use this:

declare @v decimal (5,2)=-123.45

select right('00000'+replace(replace(cast(@v as varchar(7)),'-',''),'.',''),5) as v

Open in new window

0
 

Author Comment

by:No1Coder
ID: 39275869
Almost there...one more

Have a varchar field that either contains 2 or three characters.  Need to produce 3 characters always, left aligned.  Tried cast(field as VARCHAR(3)) but is still only produces two characters when field has two.  Want trailing space instead.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39275881
Use CHAR(3).
0
 

Author Closing Comment

by:No1Coder
ID: 39275936
Great help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now