Solved

Formatting SQL output

Posted on 2013-06-25
15
292 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
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.

 
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

Suggested Solutions

Title # Comments Views Activity
How to share SSIS Package? 6 37
2016 SQL Licensing 7 41
Help creating a spatial object in SQL Server 4 23
SQL Syntax: How to force case sensitive query? 2 31
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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

803 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