Fixed length columns

Question

Post reply

Fixed length columns

Ram:)

SSCrazy

Points: 2246
More actions
July 20, 2010 at 3:04 pm

#223512

Can any body help on this
http://www.sqlservercentral.com/Forums/Topic955901-8-1.aspx
🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

OTF SSCertifiable Points: 6104 More actions · Answer 1

I would have thought Lynn's suggestion would have worked for you?

Praveen Goud Kotha Ten Centuries Points: 1141 More actions · Answer 2

Hi..

I think we can meet your requirement by using REPLICATE function(i.e..Repeats a character expression a specified number of times)

An updated query using REPLICATE is given below

select sno+replicate(' ',datalength(sno)-len(sno) as sno

,name+replicate(' ',datalength(name)-len(name) as name

,comments+replicate(' ',datalength(comments)-len(comments) as comments

from xyz

I hope it works fine..

[font="Comic Sans MS"]Praveen Goud[/font]

Ram:) SSCrazy Points: 2246 More actions · Answer 3

Praveen,

If we use Replicate we will have one issue..

if datalength(name)-len(name) is in negative values, I mean actual length of variable is greater than the given datalength.. Then we will get NULL value..

For me using Cast(... as char()) is working fine..

Correct me if I am wrong..

🙂

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

Praveen Goud Kotha (7/21/2010)
Hi..
I think we can meet your requirement by using REPLICATE function(i.e..Repeats a character expression a specified number of times)
An updated query using REPLICATE is given below
select sno+replicate(' ',datalength(sno)-len(sno) as sno
,name+replicate(' ',datalength(name)-len(name) as name
,comments+replicate(' ',datalength(comments)-len(comments) as comments
from xyz
I hope it works fine..

Praveen...

Please change your Avatar. I made that Avatar and I'd like to have exclusive use of it, please. Thank you very much.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

Ram:) (7/21/2010)
Praveen,
If we use Replicate we will have one issue..
if datalength(name)-len(name) is in negative values, I mean actual length of variable is greater than the given datalength.. Then we will get NULL value..
For me using Cast(... as char()) is working fine..
Correct me if I am wrong..

I'm confused then... why did you need to repost here if you're using Lynn's CAST/CHAR??

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ram:) SSCrazy Points: 2246 More actions · Answer 6

Hi,

Sorry.. I was reposted this before Lynn's reply.. Thanks everybody and Lynn..

Please correct me on using replicate..

🙂

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

Ram:) (7/21/2010)
Hi,
Sorry.. I was reposted this before Lynn's reply.. Thanks everybody and Lynn..
Please correct me on using replicate..

Ah... understood.

Either way is fine for performance.... the CAST method is a bit easier to read IMHO.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ram:) SSCrazy Points: 2246 More actions · Answer 8

Thanks Jeff,

The main reason why i am opting CAST is ,

SELECT 'RAMPRASAD'+REPLICATE('',5-LEN('RAMPRASAD'))-- in this case i will get NULL values.. But here i need to get only RAMPR

SELECT 'RAMPRASAD'+REPLICATE('',15-LEN('RAMPRASAD'))-- in this case no change with CAST..

🙂