right fill string characters

Question

Post reply

right fill string characters

Gimli son of Gloin

Ten Centuries

Points: 1263
More actions
May 12, 2004 at 10:21 am

#152139

I have created a table with char(x) fields. I need to right fill the strings that are inserted into those fields. How do I do that? When I insert a record, the string data is filled left to right. I want the data right aligned in the field.

Viewing 14 posts - 1 through 14 (of 14 total)

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

hoo-t SSChampion Points: 12713 More actions · Answer 1

Well, here's one way to do it.....

declare

@string char(20),

@input varchar(20),

@len int

set @input = 'example'

set @string = ' '

set @len = len(@input)

select @string = reverse(stuff(@string, 1, @len, reverse(@input)))

select @string

Steve

Gimli son of Gloin Ten Centuries Points: 1263 More actions · Answer 2

Wow! That is tremendous Steve and it works great. Thank you so much. I have spent several hours in the online manual trying to figure a way.

-Doug

Jeff Moden SSC Guru Points: 1004593 More actions · Answer 3

Here's a different way...

DECLARE @TestString VARCHAR(20) DECLARE @DesiredLEN TINYINT DECLARE @FillChar CHAR(1) DECLARE @ResultString VARCHAR(255)
SET @TestString = 'SomeString' SET @DesiredLEN = 25 SET @FillChar   ='#' --So you can "see" spaces... change to ' ' for production

SET @ResultString = RIGHT(REPLICATE(@FillChar,@DesiredLEN)+@TestString,@DesiredLEN)
PRINT @ResultString

(You said "right fill" and my original post "filled the right" with "#". Have repaired this code to work as "right justify" as you wanted).

--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)

tymberwyld SSCertifiable Points: 7810 More actions · Answer 4

Ahem...

Much easier is this...

Declare @MaxLen TinyInt

Update dbo.Table Set dbo.Table.FieldName = Replicate('0', @MaxLen - Len(LTrim(RTrim(dbo.Table.FieldName)))) + LTrim(RTrim(dbo.Table.FieldName))

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 5

Another way is like: SELECT RIGHT(SPACE(20) + 'example', 20). Probably use a LEN function or variable instead of a scalar value though...

Gimli son of Gloin Ten Centuries Points: 1263 More actions · Answer 6

SELECT RIGHT(SPACE(20) + 'example', 20)

That is amazingly simple and it works great for my need! Where you have '20' I will use the width of my CHAR(x) field and of course where you have 'example' I will use my column name.

BTW, your example also works for padding with other characters:

SELECT RIGHT('##########' + 'example', 10)

My thanks to everyone's input.

paolice SSC Veteran Points: 271 More actions · Answer 7

Hi all.

Has anyone considered using the following?

SELECT RIGHT(space(DATALENGTH("column name")) + "column name", DATALENGTH("column name"))

from "table name"

Um, replace "column name" and "table name" with your schema object names.

Jeff Moden SSC Guru Points: 1004593 More actions · Answer 8

Unless it was a text or ntext field, why would you use "DATALENGTH" over just "LEN" like some of the other replies used? Just curious...

--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)

Antares686 SSC Guru Points: 125444 More actions · Answer 9

In case you have purposefull spaces. See what happens with these

select len('a ')

select datalength('a ')

First will return 1 the second 3 becuase len ignores the trailing spaces.

paolice SSC Veteran Points: 271 More actions · Answer 10

Exactamundo Antares!

And!... this marvelous function (DATALENGTH) also returns the defined length of your table's columns. This has proved very handy in ETL work.

Jeff Moden SSC Guru Points: 1004593 More actions · Answer 11

Thanks Antares... didn't know that.

Hey paolice... how do you get DataLength to return the "defined length of your table's columns"? Couldn't find a thing about that in BOL.

--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)

paolice SSC Veteran Points: 271 More actions · Answer 12

Hey Jeff,

Yes I know this description of this function's capabilities is not found in BOL. I discovered it's 'hidden feature' after testing (my) conclusions that I drew from reading what the BOL does say about it.

Trial and Error: it's not just for scientists anymore.

Jeff Moden SSC Guru Points: 1004593 More actions · Answer 13

paolice,

Understood... I use "trial and error" a lot. But you still haven't given me a clue as to how to use DataLength to determine the defined length of a column. Particularly interested in how you do it for a VARCHAR column... mind sharing?

--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)

right fill string characters

Cookies on SQLServerCentral