Adding leading spaces to text based on length

[Hueby]

Hi all,

I'm wondering if this is possible. I want need to add Leading Spaces to Column A, based on how long the value is in that column.

If the text is 4 characters long I need to add 5 spaces in front, if the text is 5 characters long I need to add 4 spaces in front, and so on.

There are thousands of rows, so I'm trying to see if this is something I run in QA quickly. Your thoughts?

 

[vongrunt]

Add 9 spaces, then take 9 rightmost characters. For example:

select right( replicate(' ', 9) + 'blah', 9 )

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Hueby]

Thanks, what is the best method to actually execute it on the table? instead of just select...

 

[vongrunt]

Use UPDATE instead of SELECT:

UPDATE myTable
SET myColumn = RIGHT( REPLICATE(' ', 9) + RTRIM(myColumn), 9 )
-- WHERE someconditionifnecessary

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[stetou]

if I understood well your column type should be CHAR instead of nvarchar. The leading space are automatically generated.
"SQL Server char data is always stored in the data file as the full length of the defined column."see SET ANSI_PADDING

 

[vongrunt]

>The leading space are automatically generated.

Trailing, not leading...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Hueby]

Okay thanks guys. It seems this is changing my column values that I want to just add the leading spaces too... what is going on??

 

[druer]

You lost me with

It seems this is changing my column values that I want to just add the leading spaces too

What is your datatype for the column in question? (Char, varchar, nvarchar etc)
What is the length you have defined for the current column?

Vongrunt's code:

UPDATE myTable
SET myColumn = RIGHT( REPLICATE(' ', 9) + RTRIM(myColumn), 9 )
-- WHERE someconditionifnecessary

Will create a character string with 9 spaces, then add to it your current columns value with any trailing spaces trimmed off of it, then it will take only the 9 right most characters. If you have any existing leading spaces, they aren't a problem because only the 9 right most characters including the leading spaces will be taken. Meaning if you already have 5 leading spaces, adding 9 more to the front won't change the fact that you are only taking the rightmost 9 characters.

Assuming your data field allows you to store those 9 characters.

 

[Hueby]

My datatype is a varchar(10) - primary key

The code I am using:

UPDATE VN_vendor_master_mc
SET VN_vendor_master_mc.vendor_code = RIGHT(REPLICATE(' ',10) + vendor_code,10)
where VN_vendor_master_mc.Company_code = 'HWP'

When ran I get "Violation of PRIMARY KEY constraint 'PK_VN_VENDOR_MASTER_MC'. Cannot insert duplicate key in object 'VN_VENDOR_MASTER_MC'.
The statement has been terminated."

So, playing around I tried this WHERE statement instead:

where VN_vendor_master_mc.vendor_code = '1114'

...to single out a row and play. It worked, but added the space and changed to a 1115. It keeps adding a number if I continue the process.

Does that help make sense? Thank you!

 

[Hueby]

I'm an idiot. I have it fixed.. had some duplicate rows.

 

[druer]

Hopefully making mistakes doesn't make one an idiot, or I would have been locked up long ago.