How to pad a string with spaces when size of data is unknown

Question

How to pad a string with spaces when size of data is unknown

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Oracan Mr or Mrs. 500 Points: 529 More actions · Answer 1

Once again, thanks for all the suggestions. Just as you probably all are, I'm really swamped with several tasks including this one. I haven't lost interest in the thread and check it periodically and will spend the time to read the responses in detail so I can absorb all the good suggestions. So far I'm able to query data in space noodle code I've been working with. I'm going to compare it to the more simple code suggested earlier. Each field has to be set to specified lengths and the size of the data retrieved in each field could vary. Each field also has to be concatenated together so the result is one huge column. Just to give perspective on where this is all going, the end result is going to be included in an SSRS report and the .rdl sent to a customer to use. How that happens I'll soon be trained on.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

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

And based on the OPs latest reply, I see you were right about me bing wrong about the requirements. Thanks for the feedback R.P.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

suvesh.sonker 2691 SSC Rookie Points: 41 More actions · Answer 3

-- Ten digit

Declare @field as varchar(20) = '559'

Select Replace(right(replicate(' ',10) + @field ,10),' ','0')

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

suvesh.sonker 2691 (12/9/2015)
-- Ten digit
Declare @field as varchar(20) = '559'
Select Replace(right(replicate(' ',10) + @field ,10),' ','0')

In what circumstances might this be preferable to the simpler SELECT RIGHT('0000000000'+@field,10)?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

ChrisM@Work (12/9/2015)
suvesh.sonker 2691 (12/9/2015)
-- Ten digit
Declare @field as varchar(20) = '559'
Select Replace(right(replicate(' ',10) + @field ,10),' ','0')
In what circumstances might this be preferable to the simpler SELECT RIGHT('0000000000'+@field,10)?

When you have leading spaces in @Field to begin with.

Personally, I think that anyone that stores numeric values as VARCHAR may need some basic lessons in data-typing except for things like Zip Code and tax IDs and (perhaps) telephone numbers which are really codes and not numbers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Jeff Moden (12/9/2015)
ChrisM@Work (12/9/2015)
suvesh.sonker 2691 (12/9/2015)
-- Ten digit
Declare @field as varchar(20) = '559'
Select Replace(right(replicate(' ',10) + @field ,10),' ','0')
In what circumstances might this be preferable to the simpler SELECT RIGHT('0000000000'+@field,10)?
When you have leading spaces in @Field to begin with.
Personally, I think that anyone that stores numeric values as VARCHAR may need some basic lessons in data-typing except for things like Zip Code and tax IDs and (perhaps) telephone numbers which are really codes and not numbers.

You're right as always Jeff. Just as an aside, if there were a full-time college course studying TSQL, how many months would it take to qualify graduates as "competent developers"?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

ChrisM@Work (12/10/2015)
Jeff Moden (12/9/2015)
ChrisM@Work (12/9/2015)
suvesh.sonker 2691 (12/9/2015)
-- Ten digit
Declare @field as varchar(20) = '559'
Select Replace(right(replicate(' ',10) + @field ,10),' ','0')
In what circumstances might this be preferable to the simpler SELECT RIGHT('0000000000'+@field,10)?
When you have leading spaces in @Field to begin with.
Personally, I think that anyone that stores numeric values as VARCHAR may need some basic lessons in data-typing except for things like Zip Code and tax IDs and (perhaps) telephone numbers which are really codes and not numbers.
You're right as always Jeff. Just as an aside, if there were a full-time college course studying TSQL, how many months would it take to qualify graduates as "competent developers"?

Heh... leave it to me to come up with the edge cases. 😛

Concerning your question... IMHO, 12 weeks, 8 hours a day, 5 days a week, with lots and lots of hands on to start with but no one taking a course as a newbie will be a "competent developer" on graduation. Only the forge of experience accompanied by either a really good mentor or an insane level of intellectual curiosity mixed with lot's of self study (forums like this one can help in those areas a lot) can actually help someone reach that level.

I don't believe that most courses adequately prepare someone for being a really good Database Developer. For example, when was the last time that you saw any course, college/internet/book, that taught the principle of "pseudo-cursors" and Tally-Table-like structures and methods or taught some of the math (date/time math, modulus, integer division, relational multiplication/division especially in conjunction with "pseudo-cursors", tri-state logic using NULLs, Boolean Math, etc) that every heavy hitter on SSC knows? When was the last time you saw a course that taught people how to shrink the data in a partition and still have it properly indexed without imparting unnecessary free space so that when the partition is set to READ_ONLY, no free space is locked up? When was the last time you saw a course that taught how to properly use xp_CmdShell in properly written/protected stored procedures where the calling users only have PUBLIC privs and cannot call xp_CmdShell directly? Heh... and don't even get me started on the area of proper indexing and how to exploit indexes in some of the unexpected manners that we've seen on this site.

No... taking and passing even such an intensive course only qualifies people to learn. It won't make them "competent".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Jeff Moden (12/12/2015)
ChrisM@Work (12/10/2015)
Jeff Moden (12/9/2015)
ChrisM@Work (12/9/2015)
suvesh.sonker 2691 (12/9/2015)
-- Ten digit
Declare @field as varchar(20) = '559'
Select Replace(right(replicate(' ',10) + @field ,10),' ','0')
In what circumstances might this be preferable to the simpler SELECT RIGHT('0000000000'+@field,10)?
When you have leading spaces in @Field to begin with.
Personally, I think that anyone that stores numeric values as VARCHAR may need some basic lessons in data-typing except for things like Zip Code and tax IDs and (perhaps) telephone numbers which are really codes and not numbers.
You're right as always Jeff. Just as an aside, if there were a full-time college course studying TSQL, how many months would it take to qualify graduates as "competent developers"?
Heh... leave it to me to come up with the edge cases. 😛
Concerning your question... IMHO, 12 weeks, 8 hours a day, 5 days a week, with lots and lots of hands on to start with but no one taking a course as a newbie will be a "competent developer" on graduation. Only the forge of experience accompanied by either a really good mentor or an insane level of intellectual curiosity mixed with lot's of self study (forums like this one can help in those areas a lot) can actually help someone reach that level.
I don't believe that most courses adequately prepare someone for being a really good Database Developer. For example, when was the last time that you saw any course, college/internet/book, that taught the principle of "pseudo-cursors" and Tally-Table-like structures and methods or taught some of the math (date/time math, modulus, integer division, relational multiplication/division especially in conjunction with "pseudo-cursors", tri-state logic using NULLs, Boolean Math, etc) that every heavy hitter on SSC knows? When was the last time you saw a course that taught people how to shrink the data in a partition and still have it properly indexed without imparting unnecessary free space so that when the partition is set to READ_ONLY, no free space is locked up? When was the last time you saw a course that taught how to properly use xp_CmdShell in properly written/protected stored procedures where the calling users only have PUBLIC privs and cannot call xp_CmdShell directly? Heh... and don't even get me started on the area of proper indexing and how to exploit indexes in some of the unexpected manners that we've seen on this site.
No... taking and passing even such an intensive course only qualifies people to learn. It won't make them "competent".

Here’s a slightly different angle. There are plenty of developers out there who have the experience you speak of from years of working at the codeface, years experience of doing different things every six months, and yet there are precious few of them who can read and understand an execution plan. There’s mileage in this.

There’s also great mileage in teaching something akin to TSQL Recipes, or “Applied TSQL” – the concept is obvious enough that a book or two has been written about it.

Extending the recipes idea, teaching different ways of doing the same thing and knowing which ones to use and when, which will depend upon the server version and the nature of the problem. There must be six or seven ways of calculating running totals and you know which one is usually the fastest. Then there’s Cross tabs vs Pivots. IN vs EXISTS. Spend half an hour on this subject and you’d have quite a list. It’s taken us years of practice and doodling on forums to get a half decent handle on what's best, when and why, but you could teach the core concepts in a couple of weeks.

Lastly, and probably most important, show them how to quickly knock up a sensible performance comparison test and encourage them to do these tests at the drop of a hat. Once you know how, it only takes a few minutes.

Bring this all together into one 12-week course following your 12 weeks of hothouse insanity and I reckon you’d have someone who might be good enough to join their first team.

You’d have to wash ‘em first, they won’t have had time 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pietlinden SSC Guru Points: 63503 More actions · Answer 9

That would make a great SQL Saturday half-day or full day precon... I just wish someone would put together even part of that. Sure, you can look up the syntax for anything in SQL Server. The big question that you don't learn and that nobody really teaches you is WHEN and WHY to use one option instead of another. Maybe you'd have to do a lot of testing and timing to get your head around why... but then that would still require the ability to really read and understand execution plans.

So, when are you gonna start writing it? I'll even proofread it for ya. <g>

Pieter

djj SSCoach Points: 18831 More actions · Answer 10

One more thing to teach. How to ask a question. 😀

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 11

djj (12/16/2015)
One more thing to teach. How to ask a question. 😀

Did you have to ask that? 😉

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

pietlinden (12/15/2015)
That would make a great SQL Saturday half-day or full day precon... I just wish someone would put together even part of that. Sure, you can look up the syntax for anything in SQL Server. The big question that you don't learn and that nobody really teaches you is WHEN and WHY to use one option instead of another. Maybe you'd have to do a lot of testing and timing to get your head around why... but then that would still require the ability to really read and understand execution plans.
So, when are you gonna start writing it? I'll even proofread it for ya. <g>
Pieter

Hmmm...That's a really great idea.

It would need to be at least a half-day. I mean, Jeff's presentation on using xp_cmdshell is a full session. The real challenge is deciding how to thrift it down to fit within the time constraints.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

ChrisM@Work (12/15/2015)
Jeff Moden (12/12/2015)
ChrisM@Work (12/10/2015)
Jeff Moden (12/9/2015)
ChrisM@Work (12/9/2015)
suvesh.sonker 2691 (12/9/2015)
-- Ten digit
Declare @field as varchar(20) = '559'
Select Replace(right(replicate(' ',10) + @field ,10),' ','0')
In what circumstances might this be preferable to the simpler SELECT RIGHT('0000000000'+@field,10)?
When you have leading spaces in @Field to begin with.
Personally, I think that anyone that stores numeric values as VARCHAR may need some basic lessons in data-typing except for things like Zip Code and tax IDs and (perhaps) telephone numbers which are really codes and not numbers.
You're right as always Jeff. Just as an aside, if there were a full-time college course studying TSQL, how many months would it take to qualify graduates as "competent developers"?
Heh... leave it to me to come up with the edge cases. 😛
Concerning your question... IMHO, 12 weeks, 8 hours a day, 5 days a week, with lots and lots of hands on to start with but no one taking a course as a newbie will be a "competent developer" on graduation. Only the forge of experience accompanied by either a really good mentor or an insane level of intellectual curiosity mixed with lot's of self study (forums like this one can help in those areas a lot) can actually help someone reach that level.
I don't believe that most courses adequately prepare someone for being a really good Database Developer. For example, when was the last time that you saw any course, college/internet/book, that taught the principle of "pseudo-cursors" and Tally-Table-like structures and methods or taught some of the math (date/time math, modulus, integer division, relational multiplication/division especially in conjunction with "pseudo-cursors", tri-state logic using NULLs, Boolean Math, etc) that every heavy hitter on SSC knows? When was the last time you saw a course that taught people how to shrink the data in a partition and still have it properly indexed without imparting unnecessary free space so that when the partition is set to READ_ONLY, no free space is locked up? When was the last time you saw a course that taught how to properly use xp_CmdShell in properly written/protected stored procedures where the calling users only have PUBLIC privs and cannot call xp_CmdShell directly? Heh... and don't even get me started on the area of proper indexing and how to exploit indexes in some of the unexpected manners that we've seen on this site.
No... taking and passing even such an intensive course only qualifies people to learn. It won't make them "competent".
Here’s a slightly different angle. There are plenty of developers out there who have the experience you speak of from years of working at the codeface, years experience of doing different things every six months, and yet there are precious few of them who can read and understand an execution plan. There’s mileage in this.
There’s also great mileage in teaching something akin to TSQL Recipes, or “Applied TSQL” – the concept is obvious enough that a book or two has been written about it.
Extending the recipes idea, teaching different ways of doing the same thing and knowing which ones to use and when, which will depend upon the server version and the nature of the problem. There must be six or seven ways of calculating running totals and you know which one is usually the fastest. Then there’s Cross tabs vs Pivots. IN vs EXISTS. Spend half an hour on this subject and you’d have quite a list. It’s taken us years of practice and doodling on forums to get a half decent handle on what's best, when and why, but you could teach the core concepts in a couple of weeks.
Lastly, and probably most important, show them how to quickly knock up a sensible performance comparison test and encourage them to do these tests at the drop of a hat. Once you know how, it only takes a few minutes.
Bring this all together into one 12-week course following your 12 weeks of hothouse insanity and I reckon you’d have someone who might be good enough to join their first team.
You’d have to wash ‘em first, they won’t have had time 😀

Ooooo.... like Ed and Pieter said, REALLY great idea for a pre-con.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Jeff Moden (12/16/2015)
ChrisM@Work (12/15/2015)
Jeff Moden (12/12/2015)
ChrisM@Work (12/10/2015)
Jeff Moden (12/9/2015)
ChrisM@Work (12/9/2015)
suvesh.sonker 2691 (12/9/2015)
-- Ten digit
Declare @field as varchar(20) = '559'
Select Replace(right(replicate(' ',10) + @field ,10),' ','0')
In what circumstances might this be preferable to the simpler SELECT RIGHT('0000000000'+@field,10)?
When you have leading spaces in @Field to begin with.
Personally, I think that anyone that stores numeric values as VARCHAR may need some basic lessons in data-typing except for things like Zip Code and tax IDs and (perhaps) telephone numbers which are really codes and not numbers.
You're right as always Jeff. Just as an aside, if there were a full-time college course studying TSQL, how many months would it take to qualify graduates as "competent developers"?
Heh... leave it to me to come up with the edge cases. 😛
Concerning your question... IMHO, 12 weeks, 8 hours a day, 5 days a week, with lots and lots of hands on to start with but no one taking a course as a newbie will be a "competent developer" on graduation. Only the forge of experience accompanied by either a really good mentor or an insane level of intellectual curiosity mixed with lot's of self study (forums like this one can help in those areas a lot) can actually help someone reach that level.
I don't believe that most courses adequately prepare someone for being a really good Database Developer. For example, when was the last time that you saw any course, college/internet/book, that taught the principle of "pseudo-cursors" and Tally-Table-like structures and methods or taught some of the math (date/time math, modulus, integer division, relational multiplication/division especially in conjunction with "pseudo-cursors", tri-state logic using NULLs, Boolean Math, etc) that every heavy hitter on SSC knows? When was the last time you saw a course that taught people how to shrink the data in a partition and still have it properly indexed without imparting unnecessary free space so that when the partition is set to READ_ONLY, no free space is locked up? When was the last time you saw a course that taught how to properly use xp_CmdShell in properly written/protected stored procedures where the calling users only have PUBLIC privs and cannot call xp_CmdShell directly? Heh... and don't even get me started on the area of proper indexing and how to exploit indexes in some of the unexpected manners that we've seen on this site.
No... taking and passing even such an intensive course only qualifies people to learn. It won't make them "competent".
Here’s a slightly different angle. There are plenty of developers out there who have the experience you speak of from years of working at the codeface, years experience of doing different things every six months, and yet there are precious few of them who can read and understand an execution plan. There’s mileage in this.
There’s also great mileage in teaching something akin to TSQL Recipes, or “Applied TSQL” – the concept is obvious enough that a book or two has been written about it.
Extending the recipes idea, teaching different ways of doing the same thing and knowing which ones to use and when, which will depend upon the server version and the nature of the problem. There must be six or seven ways of calculating running totals and you know which one is usually the fastest. Then there’s Cross tabs vs Pivots. IN vs EXISTS. Spend half an hour on this subject and you’d have quite a list. It’s taken us years of practice and doodling on forums to get a half decent handle on what's best, when and why, but you could teach the core concepts in a couple of weeks.
Lastly, and probably most important, show them how to quickly knock up a sensible performance comparison test and encourage them to do these tests at the drop of a hat. Once you know how, it only takes a few minutes.
Bring this all together into one 12-week course following your 12 weeks of hothouse insanity and I reckon you’d have someone who might be good enough to join their first team.
You’d have to wash ‘em first, they won’t have had time 😀
Ooooo.... like Ed and Pieter said, REALLY great idea for a pre-con.

I haven't been to the States for a while. A half-day of this would be a good reason to go.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden