sql string space padding

I have a char(10) columnA with values:
George
Tom
David

problem is when I take Len(columnA) i get:
5
3
5

how to best change type or pad spaces such that Len(columnA) is always = 10?
LVL 3
prequel_serverAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just a thought ... why do you need len(ColumnA) = 10?  
Left or right padded spaces?

If you're doing this for an export, to guarantee ten spaces you can use ColumnA + SPACE(10 - Len(ColumnA))
0
prequel_serverAuthor Commented:
I have a program that only works if the length of that column is 10.
0
Éric MoreauSenior .Net ConsultantCommented:
you can use DataLength:

DECLARE @x CHAR(10)
SET @x = 'tom'
SELECT @x, LEN(@x), DATALENGTH(@x)

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Scott PletcherSenior DBACommented:
LEN(columnA + '.') - 1

The potential issue with DATALENGTH is if you ever change the data type to nchar(10), you'll get "20" as the result instead of "10".


SELECT LEN(columnA + '.') - 1
FROM (
    SELECT CAST('George' AS char(10)) AS columnA UNION ALL
    SELECT CAST('Tom' AS char(10)) AS columnA UNION ALL
    SELECT CAST('David' AS char(10)) AS columnA
) AS test_data
0
prequel_serverAuthor Commented:
Sorry i wasnt clear. My issue is not what len() returns its just that i want my column to be padded with trailing spaces such that it always adds up to 10 chars.

So David should be David and the 5 spaces after it
Tom would have 7 spaces after it
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>So David should be David and the 5 spaces after it
The T-SQL I posted in the first comment does that,
0
Scott PletcherSenior DBACommented:
LEFT(columnA + SPACE(10), 10)
0
prequel_serverAuthor Commented:
@Scott, I test tried that but unfortunately it doesn't pass the litmus test. of:

LEN (  LEFT(columnA + SPACE(10), 10)  )

still getting various sizes based on the values in the columnA
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>still getting various sizes based on the values in the columnA
Please give us a sample data set of ColumnA values, and what your expected results are.
0
Scott PletcherSenior DBACommented:
>>
@Scott, I test tried that but unfortunately it doesn't pass the litmus test. of:
 LEN (  LEFT(columnA + SPACE(10), 10)  )
 still getting various sizes based on the values in the columnA
<<

>> My issue is not what len() returns its just that i want my column to be padded with trailing spaces such that it always adds up to 10 chars. <<

I corrected for the LEN() issue earlier, then you said to ignore length and just output 10 chars.

It seems you want contradictory things.  The LEN() function will always ignore trailing spaces.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
prequel_serverAuthor Commented:
Sorry i didnt know that len() ignors all trailing spaces. Is there another test i use to check?
0
Scott PletcherSenior DBACommented:
My version is:

SELECT LEN(columnA + '.') - 1

putting a non-space at the end so SQL counts all characters from the column, including trailing spaces.  Adding a '.' of course changes the trailing spaces into embedded spaces, and thus they are no longer ignored.
0
prequel_serverAuthor Commented:
thanks but I don't want an embedded space '.' at the end of the value, I only want enough spaces to fill a 10 char field.
Len() ignores trailing spaces.
is there another length function or nested functions that can count the trailing spaces in a string?
0
Scott PletcherSenior DBACommented:
I'm subtracting 1 from the length to drop the '.' back out; the '.' is just being used to force a non-space character at the end of the value so that LEN() can count all the other chars.
0
prequel_serverAuthor Commented:
Correct but not exactly what im looking for. I guess i was looking for a test to prove that the field is in fact 10 chars in length.  I thought there would be a function that does count trailing spaces but maybe im wrong. Anyway thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.