February 29, 2012 at 3:19 pm
When selecting character data, I'm trying to pad the data with spaces so that the length of what I'm returning is the same for all rows. This will ultimately go into a report that has all fixed length columns so the size of the data returned for each field has to be consistent.
ie. Col1 = length of 5, Col2 = length of 10, etc.
I've tried STR, RPAD, Replace and a variety of combinations but none are working.
Data Example:
XXAAB
XX
XXA
Desired result is a field length of 5 for all data returned:
XXAAB
XX (with 3 spaces)
XXA (with 2 spaces)
Some of the code tried:
select
rpad(en.fieldname,5,'')
str(en.fieldname,5,'')
rpad(en.fieldname,5,' ')
str(en.fieldname,5,' ')
Common error received:
Argument data type varchar is invalid for argument 3 of str function.
Is there another way to represent argument 3 for spaces or do I need another function(s)?
February 29, 2012 at 6:53 pm
Thanks for the reply, but could you be more specific? That article leaves my head spinning without the answer.
March 1, 2012 at 6:08 am
Thanks for the response. This will add the spaces, but I need to concatenate each field together and when I use the CAST option, the fields all appear next to each other instead of being fixed width with 5 spaces for each field. Can anyone offer how to accomplish this?
Result so far:
XXAABXXXXA
Desired result:
XXAABXX XXA
March 1, 2012 at 6:47 am
We don't know what you're trying, as you did not post your code here.
CAST should work. Check this one:
declare @t table (col1 varchar(5), col2 varchar(5), col3 varchar(5))
insert @t select 'XXAAB','XX','XXA'
-- This will return "XXAABXXXXA"
select col1 + col2 + col3 from @t
-- This will return "XXAABXX XXA "
select cast(col1 as CHAR(5))+ cast(col2 as CHAR(5))+ cast(col3 as char(5)) from @t
March 1, 2012 at 6:48 am
CELKO is correct. Cast your columns as char(n). An example
SELECT CAST('XXAAB' as char(10)) + CAST('XX' as char(15))+ CAST('XXA' as char(5))+ 'XX'
Returns
XXAAB XX XXA XX
I added the XX at the end to show the padding after the XXA.
March 1, 2012 at 2:53 pm
Thanks for all the responses. I ended up doing the following and am still working on it as it is just part of what I have to do. Then I'll verify all is working accordingly.
This checks the length and adds spaces to the field if the length of the data is less than the field size.
en.field1 + SPACE(18-len(left(en.field1,18)))+
en.field2 + SPACE(12-len(left(en.field2,12)))+ next field
March 1, 2012 at 5:05 pm
The answer has been given multiple times already, so why are you still trying to make this so complicated? Just use convert or cast to convert the string value to a type of char(n), with n being the desired length of the string. A value in a column of type char(n) that has a length less than the column's maximum length is always right padded with spaces. Then simply concatenate each of the converted values using the +-operator (string concatenation).
So for example:
select convert(char(5), col1) + convert(char(10), col2) + convert(char(4), col3)
from dbo.MyTable
This will (provided none of the 3 columns has a null value) always return a single string of 19 (= 5 + 10 + 4) characters long, no matter the length of the strings in col1, col2 and col3.
March 1, 2012 at 6:58 pm
The answer has been given multiple times already,
I'm not so sure that's true. It sounds like the OP has unknown length data and wants to size the output based soley on the max width of the unknown data.
One way to do this is to fire the output into a Temp Table using SELECT/INTO and then read the max width of the data in each column it created. Then you can do a final SELECT using a bit of dynamic SQL based on the max width of the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2012 at 10:05 pm
Jeff Moden (3/1/2012)
It sounds like the OP has unknown length data and wants to size the output based soley on the max width of the unknown data.
That's not what the OP says in post #1260471. The pseudo code that he posts as "what he ended up with" does no attempt to compare a value's length with any other values' lengths, only each values own length is used. Plus they are even different lengths per column:
Oracan (3/1/2012)
en.field1 + SPACE(18-len(left(en.field1,18)))+en.field2 + SPACE(12-len(left(en.field2,12)))+ next field
Apparantly field1 must have a fixed length of 18 while field2 should have one of 12. i.e. Each field has it's own specific length and those lengths are not dependent on any data in another row nor in another column. He is only right padding the strings to a set length per column. And for that purpose the solution is needlessly complex. I still think Joe got it right the first time around.
March 2, 2012 at 6:44 am
R.P.Rozema (3/1/2012)
Jeff Moden (3/1/2012)
It sounds like the OP has unknown length data and wants to size the output based soley on the max width of the unknown data.That's not what the OP says in post #1260471. The pseudo code that he posts as "what he ended up with" does no attempt to compare a value's length with any other values' lengths, only each values own length is used. Plus they are even different lengths per column:
Oracan (3/1/2012)
en.field1 + SPACE(18-len(left(en.field1,18)))+en.field2 + SPACE(12-len(left(en.field2,12)))+ next field
Apparantly field1 must have a fixed length of 18 while field2 should have one of 12. i.e. Each field has it's own specific length and those lengths are not dependent on any data in another row nor in another column. He is only right padding the strings to a set length per column. And for that purpose the solution is needlessly complex. I still think Joe got it right the first time around.
If you combine the title of this thread ("unknown" and "size" being the operative words), it's exactly what the op is asking for. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 2, 2012 at 7:06 am
Looks like OP is happy with his SPACE-noodles and has lost the interest to the thread, while the busy people (us we all are) still in the process of discussion of his requirements.
But, from the code he provided, looks like he has predefined fix lengths (he is using constants in his SPACE-noodles), therefore CAST to CHAR should work. I just wonder, may be he convert it back to varchar along the line...
Jeff, I think his "unkown" is referred to the value in the same column, so his question is, if he has 1, 2, 3, 4 or 5 letter word in the varchar(5) column, how to output all values right-padded to the 5 character string. Again, it's my understanding only and it can be wrong 😉
March 2, 2012 at 7:51 am
Jeff Moden (3/2/2012)
R.P.Rozema (3/1/2012)
Jeff Moden (3/1/2012)
It sounds like the OP has unknown length data and wants to size the output based soley on the max width of the unknown data.That's not what the OP says in post #1260471. The pseudo code that he posts as "what he ended up with" does no attempt to compare a value's length with any other values' lengths, only each values own length is used. Plus they are even different lengths per column:
Oracan (3/1/2012)
en.field1 + SPACE(18-len(left(en.field1,18)))+en.field2 + SPACE(12-len(left(en.field2,12)))+ next field
Apparantly field1 must have a fixed length of 18 while field2 should have one of 12. i.e. Each field has it's own specific length and those lengths are not dependent on any data in another row nor in another column. He is only right padding the strings to a set length per column. And for that purpose the solution is needlessly complex. I still think Joe got it right the first time around.
If you combine the title of this thread ("unknown" and "size" being the operative words), it's exactly what the op is asking for. 😉
Ok I have a question just for fun.
SELECT LEFT(TEXTFIELD + REPLICATE(' ',(SELECT MAX(LEN(TEXTFIELD)) FROM ZTMP_TEST1_DATA)),(SELECT MAX(LEN(TEXTFIELD)) FROM ZTMP_TEST1_DATA)) FROM ZTMP_TEST1_DATA
I see that the select(max(len(... executes twice in the plan. Now for sure thats a wasteful solution (but comparable to copying to a temp table, getting max, then running the select a third time to fill it out with the dynamic sql), but since its just one select statement, is there any way to make that select max happen once?
Sure, SET @max-2 = SELECT MAX(LEN(TEXTFIELD)) FROM ZTMP_TEST1_DATA maybe. But would for instance it be nonstandard for SQL implementers to fold the common subexpression into one operation?
March 2, 2012 at 8:09 am
Just for the sake of the discussion, because this is a useless alternative for the OP's question. You could make it execute the max(len()) only once like this:
SELECT LEFT(t2.TEXTFIELD + REPLICATE(' ', t1.MaxLen), t1.MaxLen)
FROM (
SELECT MAX(LEN(TEXTFIELD)) as MaxLen FROM ZTMP_TEST1_DATA
) t1
CROSS JOIN ZTMP_TEST1_DATA t2
March 2, 2012 at 8:18 am
R.P.Rozema (3/2/2012)
Just for the sake of the discussion, because this is a useless alternative for the OP's question. You could make it execute the max(len()) only once like this:
SELECT LEFT(t2.TEXTFIELD + REPLICATE(' ', t1.MaxLen), t1.MaxLen)FROM (
SELECT MAX(LEN(TEXTFIELD)) as MaxLen FROM ZTMP_TEST1_DATA
) t1
CROSS JOIN ZTMP_TEST1_DATA t2
Nice solution!
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply