exporting numbers to fixed-width field text file left aligns numb

microsoft.public.sqlserver.programming

18 Aug 2005 3:51 PM

AndreasB

I export a Table containing numbers to a fixed-width field text file using
the Import-Export Wizard. The numeric fields are automatically left aligned
just like the char fields. Is there a way to right align the numeric fileds
and keep the char ones left aligned?

18 Aug 2005 4:17 PM

Aaron Bertrand [SQL Server MVP]

CREATE TABLE #foo(a INT, b CHAR(4), c INT)
INSERT #foo SELECT 4,'foo', 233278
INSERT #foo SELECT 327823,'bar', 45
SELECT
    a = CONVERT(CHAR(10), COALESCE(SPACE(10-len(a))+RTRIM(a), SPACE(10))),
    b,
    c = CONVERT(CHAR(10), COALESCE(SPACE(10-len(c))+RTRIM(c), SPACE(10)))
FROM #foo
DROP TABLE #foo

Show quote

"AndreasB" <Andre***@discussions.microsoft.com> wrote in message
news:E2680EB0-22CB-4F78-9A31-17CA5D9D8169@microsoft.com...
>I export a Table containing numbers to a fixed-width field text file using
> the Import-Export Wizard. The numeric fields are automatically left
> aligned
> just like the char fields. Is there a way to right align the numeric
> fileds
> and keep the char ones left aligned?