remove space between columns - 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

SQL Server Development (2000)

remove space between columns

Author

Topic

monteSQL
Starting Member

2 Posts

Posted - 2008-07-23 : 16:47:56

Hi,

I would like to know if there is a way of formatting the output of a query to remove blank spaces between columns

select per_id, per_name from emp

result:

per_id per_name
-------------------------- ----------------------------
1001 test

I would like the result to be

per_id per_name
----------------
1001 test

Please let me know if this is possible

Thanks for your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-23 : 17:23:01

You can use LTRIM and RTRIM to trim blanks.

It's best to post your data examples using code tags to retain the formatting.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-23 : 19:04:40

you can manually delete spaces in output result.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-23 : 19:11:29

quote:
Originally posted by sodeep

you can manually delete spaces in output result.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-23 : 19:14:41

quote:
Originally posted by tkizer

quote:
Originally posted by sodeep

you can manually delete spaces in output result.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

He/She is talking about output which displays when view the results in text format.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-23 : 19:19:01

If he/she was looking for an answer that required manual work, they wouldn't have posted a question. Of course they already realize they can trim it manually.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

monteSQL
Starting Member

2 Posts

Posted - 2008-07-23 : 23:04:56

We can use ltrim and rtrim to remove spaces in the field but I have to remove the fixed width delimiter between the 2 output columns

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-24 : 02:05:01

This depends to the length of the column names.

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-24 : 03:31:49

quote:
Originally posted by monteSQL

We can use ltrim and rtrim to remove spaces in the field but I have to remove the fixed width delimiter between the 2 output columns

Why do you want to do this?
Are you trying to copy the result to text file?

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-24 : 12:33:25

quote:
Originally posted by monteSQL

We can use ltrim and rtrim to remove spaces in the field but I have to remove the fixed width delimiter between the 2 output columns

This is unnecessary. If you are exporting to a file, then just use bcp.exe or DTS (SSIS if 2005). Handle your formatting through the column delimiter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-24 : 14:25:54

quote:
Originally posted by monteSQL

We can use ltrim and rtrim to remove spaces in the field but I have to remove the fixed width delimiter between the 2 output columns

are all the fields in a column of fixed width?

SELECT CONVERT(VARCHAR(), COLUMN_A) + CONVERT(VARCHAR(), COLUMN_B) + CONVERT(VARCHAR(), COLUMN_C)...
FROM YOUR_TABLE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 01:07:36

quote:
Originally posted by rohitkumar

quote:
Originally posted by monteSQL

We can use ltrim and rtrim to remove spaces in the field but I have to remove the fixed width delimiter between the 2 output columns

are all the fields in a column of fixed width?

SELECT CONVERT(VARCHAR(), COLUMN_A) + CONVERT(VARCHAR(), COLUMN_B) + CONVERT(VARCHAR(), COLUMN_C)...
FROM YOUR_TABLE

no need of concatenation just to remove spaces in result pane.
and even if you're trying to concatenate you need to make sure you handle nullable columns in above query else result will be NULL if you've not changed default setting of concat null yields null

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-25 : 03:02:38

quote:
Originally posted by rohitkumar

quote:
Originally posted by monteSQL

We can use ltrim and rtrim to remove spaces in the field but I have to remove the fixed width delimiter between the 2 output columns

are all the fields in a column of fixed width?

SELECT CONVERT(VARCHAR(), COLUMN_A) + CONVERT(VARCHAR(), COLUMN_B) + CONVERT(VARCHAR(), COLUMN_C)...
FROM YOUR_TABLE

Dont you need proper size for VARCHAR?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources