Fixed File Format Output - SQL Server Forums

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

Transact-SQL (2000)

Fixed File Format Output

Author

Topic

girims
Starting Member

5 Posts

Posted - 2005-12-08 : 18:35:09

I need to output query results to a Fixed File Format Text file thru ASP. Instead of writing a code to check length and pad extra space for each recordset field is there a better way to achieve this thru TSQL ??

- Giri

Giri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-08 : 20:13:40

is the column varchar ? then use convert or cast to char

-----------------
[KH]
Guys, where are we right now ?

girims
Starting Member

5 Posts

Posted - 2005-12-08 : 21:10:45

quote:
Originally posted by khtan

is the column varchar ? then use convert or cast to char

-----------------
[KH]
Guys, where are we right now ?

Thanks it helped, however, I have 3 different column types; Numeric, Date, Nvarchar. How can I pad space before or after data since I need to right justify Numeric and left justify rest.

Giri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-08 : 21:27:30

see the example below. Change the formating of the date to your requirement

declare	@num	numeric(6,2),
	@dt	datetime,
	@nv	nvarchar(10)

select	@num	= 135.34,
	@dt	= getdate(),
	@nv	= 'nvarchar'

select	'[' + right(space(10) + rtrim(convert(char(10), @num)), 10) + ']', 	-- right align in a 10 char fix length
	'[' + convert(char(20), convert(char(10), @dt, 121)) + ']',		-- left align date in 20 char fix length
	'[' + convert(nchar(20), @nv) + ']'					-- left align a 10 char to a 20 char fix length

-----------------
[KH]
Guys, where are we right now ?

girims
Starting Member

5 Posts

Posted - 2005-12-09 : 12:03:01

quote:

Originally posted by khtan

see the example below. Change the formating of the date to your requirement

declare	@num	numeric(6,2),
	@dt	datetime,
	@nv	nvarchar(10)

select	@num	= 135.34,
	@dt	= getdate(),
	@nv	= 'nvarchar'

select	'[' + right(space(10) + rtrim(convert(char(10), @num)), 10) + ']', 	-- right align in a 10 char fix length
	'[' + convert(char(20), convert(char(10), @dt, 121)) + ']',		-- left align date in 20 char fix length
	'[' + convert(nchar(20), @nv) + ']'					-- left align a 10 char to a 20 char fix length

-----------------
[KH]
Guys, where are we right now ?

Thank you very much it's working great. Can you please give me a little bit of insight on how this is works. I am quite puzzeled since we need to do a check on Length of string before we add extra space in VBSCRIPT and we don't have to do it now.

Giri

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-09 : 23:18:40

basically char will give u fixed length where as varchar will be variable length.
See code below

declare
	@char		char(10),
	@varchar	varchar(10)

select	@char	= 'test',
	@varchar	= 'test'

select '[' + @char + ']', '[' + @varchar + ']'

-----------------
[KH]
Where Am I ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources