SELECT-Query. Truncate result? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT-Query. Truncate result?

Author

Topic

mazta
Starting Member

6 Posts

Posted - 2007-10-24 : 09:47:00

Hey,

I'm sort of a SQL-rookie, both to SQL it self and to this forum, so if this subject is something that's been covered in here before, could you let me know where?

What I want to do, is to truncate the spacing’s, which some schmuck has set up, that fills out all my columns to the same width (see examples below). The important thing is that I don't manipulate the actual table, just the result table. This would help a lot when other users export the results to different text editors, no other application is being used to extract the data (except for SQL Query Analyser that is).

The query looks something like this:

USE eBC_Transaction
GO

DECLARE @first_date char(8), @last_date char (8)
SET @first_date = '20071001'
SET @last_date = '20071024'

SELECT DISTINCT ProcessingDate, SenderId, ReceiverId, DocumentDate, DocumentId, DocumentUnits, 0.65 AS Price
FROM Transaction_Production
WHERE SenderId = 'XXXXXXXXX'
AND ProcessingDate BETWEEN @first_date AND @last_date
AND DocumentId NOT LIKE '070___' --Sort out test transactions
AND ReceiverId NOT LIKE '000000%'
AND ReceiverId NOT LIKE '11111111%'
ORDER BY ReceiverId, DocumentDate

Acctual result:
5XXXXXXXXX09______________SPACING______________2120000845______________SPACING______________ 20070705______________SPACING______________

Wanted result:
5XXXXXXXXX09 2120000845 20070705

Is this possible?

Thanks in advance.

Pontus

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 09:55:15

You must have the result in text mode. Press Ctrl - D to switch to grid mode. Don't worry about the extra spacing of the datetime column that's just how Query Analyzer present the result in text mode.

KH
[spoiler]Time is always against us[/spoiler]

mazta
Starting Member

6 Posts

Posted - 2007-10-24 : 10:17:51

Thanks for your reply!

Though the result was posted from Gridmode. And the sad thing about the table-setup, is that all the columns are set to char(50) (except for DocumentUnits, which is a Int), which I guess is the reason for all the "cellpadding". Is there some way to get around this? Or do I need a application format-function to set it up properly?

/Pontus

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 10:34:57

use varchar() ?

KH
[spoiler]Time is always against us[/spoiler]

mazta
Starting Member

6 Posts

Posted - 2007-10-24 : 10:43:11

It's from a transaction databas that contains some 10,000,000 transaction, so altering acctual table isn't in my intrest. But Google is my friend, and "RTRIM(ProcessingDate) AS ProcessingDate" solved the problem!

Thanks for your time.

/Pontus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources