Pad nvarchar with trailing spaces

Question

Post reply

Pad nvarchar with trailing spaces

Glenn

SSC Veteran

Points: 289
More actions
November 6, 2010 at 9:20 pm

#228670

I'd like to pad an nvarchar field with trailing spaces to make the length a uniform 23. Is there anything
equivalent to what I do in excel as in =A2&REPT(" ",23-LEN(A2))?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

CONVERT(nchar(23), <FieldToConvert>)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

ColdCoffee

SSC-Dedicated

Points: 39972

November 7, 2010 at 8:02 am

#1247452

How about this?

SELECT Col1 + SPACE(32-LEN(Col1))

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Glenn (11/6/2010)
I'd like to pad an nvarchar field with trailing spaces to make the length a uniform 23. Is there anything
equivalent to what I do in excel as in =A2&REPT(" ",23-LEN(A2))?
Thanks

As others have clearly demonstrated, it is certainly possible in a number of ways, but I ask myself: what is the point of using a nvarchar field? Use a nchar(23) field, so the padding is automatically performed for you. I'm not sure, but this can be an optimization for your database engine, as it doesn' have to check the length of your variable field anymore.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

ColdCoffee (11/7/2010)
How about this?
SELECT Col1 + SPACE(32-LEN(Col1))

That can result in a string longer than 23 characters if there are already trailing spaces.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

This should fix that method Gail, although I suspect a convert would yield better performance.

SELECT Left(Col1 + SPACE(32), 32)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/