Solved

Formatting SQL output

Posted on 2013-06-25
15
290 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

914 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

16 Experts available now in Live!

Get 1:1 Help Now