Solved

sql string space padding

Posted on 2014-10-31
15
156 Views
Last Modified: 2014-11-03
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?
0
Comment
Question by:prequel_server
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40416001
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
 
LVL 3

Author Comment

by:prequel_server
ID: 40416049
I have a program that only works if the length of that column is 10.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40416054
you can use DataLength:

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

Open in new window

0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40416161
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
 
LVL 3

Author Comment

by:prequel_server
ID: 40416234
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40416237
>So David should be David and the 5 spaces after it
The T-SQL I posted in the first comment does that,
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40416270
LEFT(columnA + SPACE(10), 10)
0
 
LVL 3

Author Comment

by:prequel_server
ID: 40416397
@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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40416402
>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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40416414
>>
@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
 
LVL 3

Author Comment

by:prequel_server
ID: 40416446
Sorry i didnt know that len() ignors all trailing spaces. Is there another test i use to check?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40416450
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
 
LVL 3

Author Comment

by:prequel_server
ID: 40417375
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40419465
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
 
LVL 3

Author Comment

by:prequel_server
ID: 40419482
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SHOWPLAN permission denied in database 'AdventureWorks'. 13 111
SQL query with cast 38 54
SSRS Subscription jobs disabled, yet still running 4 50
SQL Recursion 6 20
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question