prequel_server
asked on
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?
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?
ASKER
I have a program that only works if the length of that column is 10.
you can use DataLength:
DECLARE @x CHAR(10)
SET @x = 'tom'
SELECT @x, LEN(@x), DATALENGTH(@x)
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
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
ASKER
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
So David should be David and the 5 spaces after it
Tom would have 7 spaces after it
>So David should be David and the 5 spaces after it
The T-SQL I posted in the first comment does that,
The T-SQL I posted in the first comment does that,
LEFT(columnA + SPACE(10), 10)
ASKER
@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
LEN ( LEFT(columnA + SPACE(10), 10) )
still getting various sizes based on the values in the columnA
>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.
Please give us a sample data set of ColumnA values, and what your expected results are.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry i didnt know that len() ignors all trailing spaces. Is there another test i use to check?
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.
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.
ASKER
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?
Len() ignores trailing spaces.
is there another length function or nested functions that can count the trailing spaces in a string?
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.
ASKER
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.
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))