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
Solved

Formatting SQL output

Posted on 2013-06-25
15
293 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

856 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