Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Formatting SQL output

Posted on 2013-06-25
15
Medium Priority
?
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 49

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 49

Expert Comment

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

so
12345.67

would become

+1234567
0
 
LVL 49

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 49

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
 
LVL 27

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 27

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 27

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 27

Accepted Solution

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

Author Closing Comment

by:No1Coder
ID: 39275936
Great help.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.

722 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