Solved

sql string space padding

Posted on 2014-10-31
15
154 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

816 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now