How to Right-Justify certain columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Right-Justify certain columns

Author

Topic

stevehatpa
Starting Member

21 Posts

Posted - 2008-06-13 : 13:00:49

I am writing a query in SQL Analyzer to put into a DTS Package. Everything works perfectly in my query, except for the fact that all of the columns are left-justified, and I need to right justify four of the columns that have numbers. These columns are "Land", "Improvement", "Total", and "Farmstead".

Here is the query in question:

Select PARCEL,
DISTRICT,
NAME_INDEX,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
LAND,
IMPROVEMENT,
TOTAL,
(CASE WHEN vwtyroneexportnew.homestead>0 THEN 'Homestead' ELSE ''END) as [HOMESTEAD],
FARMSTEAD,
(CASE WHEN vwtyroneexportnew.clean_green= 'A' THEN 'C&G' Else ''END) as [CLEAN_GREEN]

FROM vwtyroneexportnew

WHERE year_id=year(getdate())

Any help would be greatly appreciated.

-Steve H.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 13:06:15

what do you mean by right justified? do you mean presence of white spaces/ if yes use

LTRIM(Field) to remove leading spaces and RTRIM(field) to remove trailing spaces

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-13 : 13:09:15

You can go thru tools\options\query results\results to text
and choose to right-align numerics there. Other than that,
formatting should be done in the front end.

Jim

stevehatpa
Starting Member

21 Posts

Posted - 2008-06-13 : 13:10:42

Sorry, I mean to have columns look like this:
2400.00
385.00
123454.34

Currently they look like this:
2400.00
385.00
123454.34

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-13 : 13:11:32

Right/Left Justification matters only if you want to show them in Reports/Webpage or export to text file. Otherwise you dont need this

Madhivanan

Failing to plan is Planning to fail

stevehatpa
Starting Member

21 Posts

Posted - 2008-06-13 : 13:19:53

This query will be exported to fixed-width text file with a scheduled DTS package. The query above helps execute the package. So I do need the numeric columns right-justified.

Thanks,

Steve H.

stevehatpa
Starting Member

21 Posts

Posted - 2008-06-13 : 13:49:54

Could someone tell me if the following statement seems correct for one of the numeric columns that needs to be 25 characters in width and 2 decimal places?

(CASE WHEN vwtyroneexportnew.farmstead>0 THEN (str(farmstead,25,2)) ELSE '' END) as FARMSTEAD

Thanks in advance.

-Steve H

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-13 : 14:13:30

[code]

select
a.num,
fixed_number =
right(' '+convert(varchar(20),a.num),12)
from
(
--Test Data
select num = 2400.00 union all
select num = 385.00 union all
select num = 123454.34
) a

Results:

num fixed_number
---------- ------------
2400.00 2400.00
385.00 385.00
123454.34 123454.34

(3 row(s) affected)

[/code]

CODO ERGO SUM

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-13 : 14:28:26

This may be overkill, but it inserts 25 spaces when value is null or 0
DECLARE @value numeric (16,2)
SET @value = 0
DECLARE @width int
SET @Width = 25

SELECT CASE WHEN @Value > 0
THEN REPLICATE(' ',@width- ISNULL(LEN(@value) ,0)) + ISNULL(CONVERT(varchar(25), @value),'')
ELSE REPLICATE(' ',@width)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources