Limit Character returns - 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)

Limit Character returns

Author

Topic

techglider
Starting Member

26 Posts

Posted - 2010-01-25 : 11:39:40

I am concatenating a few columns in sql together, but i want to limit the length of certain returned columns to a certain number of characters..

I don't want to truncate or trim.. So if i have a name that is longer or shorter than lets say 20 characters.. It will fill in the excess space with white space.. and if its too long i will just do a LEN(Name) <= 20..

so if the name is .. 'Alan Smith'
It will make the column being appended 'Alan Smith '

versacestl
Starting Member

10 Posts

Posted - 2010-01-25 : 12:02:32

That was supposed to be 'Alan Smith...............' = 20 char length

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-25 : 12:21:25

Try this:
select convert(char(20),left('Alan Smith'+space(20),20))
select convert(char(20),left('AlanSmith and the fabulous characters'+space(20),20))

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-25 : 12:29:17

WHY do you want to put empty white space in your database?

For Presentation Reasons?

That would NOT be a good enough Answer

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

versacestl
Starting Member

10 Posts

Posted - 2010-01-25 : 12:31:37

This is not being put in the database.. This is being Put into a text file..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-25 : 12:33:28

Yes I thought that is needed for fixed lenght output.

No, you're never too old to Yak'n'Roll if you're too young to die.

versacestl
Starting Member

10 Posts

Posted - 2010-01-25 : 12:43:11

Yes, apparently the client uses a long string of unreadable to the eye length info for processing.. I just created a temp table with the max char length and outputted to ragged right.
Solved.. Dunno why i even asked this question but thanks for the responses.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-25 : 12:43:57

how are you putting it into the text file?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-25 : 12:53:17

Do you have two logins?
versacestl / techglider

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-25 : 13:18:13

quote:
Originally posted by versacestl

Yes, apparently the client uses a long string of unreadable to the eye length info for processing.. I just created a temp table with the max char length and outputted to ragged right.
Solved.. Dunno why i even asked this question but thanks for the responses.

It could be needed for a mainframe input

Blow off the temp table and create a view

CREATE VIEW <View name>
AS

SELECT LEFT(COALESCE(charCol,'')+SPACE(20))
, RIGHT(SPACE(20)+CONVERT((varchar(20),COALESCE(intChar,0))

You need to handle dates, numbers, ect

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

versacestl
Starting Member

10 Posts

Posted - 2010-01-25 : 15:28:14

apparently i do have two login's lol

versacestl
Starting Member

10 Posts

Posted - 2010-01-25 : 15:28:58

quote:
Originally posted by X002548

how are you putting it into the text file?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Putting it in a text file by exporting the temp table in the management studio. No need to create a view since this is only temporarily needed on the client side for their testing.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-25 : 15:36:36

do you mind posting all of your code?

Oh, and "it's only a 1 time thing", is the BIGGEST Lie in the business

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources