Using the SPACE and REPLICATE String Functions

If you need to put more than a space or two into a column or a variable SPACE is a lot more elegant (and readable) than just embedding a whole line of spaces. REPLICATE is the big brother to SPACE, able to generate copies of any character needed. Both have their quirks though, and we'll explore at least some of those in our lesson today.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Trent on 10/28/2010

Never seen this feature before. Very cool and very good examples. I really enjoyed it.

Dean Dodson on 10/28/2010

Nice tidbit of info, thanks!

Matt Karp on 10/28/2010

Basic, but useful.

dfortier on 10/28/2010

Today I learned how to easily right justify numeric values, thanks.

DaveClark on 10/28/2010

I liked the right justification of numbers. I never thought to do that.

Michael P. Antonovich on 10/28/2010

The maximum length of text in SQL Server 7.0 Text field is 8,000 characters.

Michael Prince on 10/28/2010

This speaker is the best. Please keep up the good work. Thanks.

kalapala baburao on 10/28/2010

ok

M on 10/29/2010

It's always nice to learn something new!

Albert on 10/29/2010

good!

Ousainou Jallow on 10/29/2010

it was good

Subir on 10/30/2010

good, can you please provide some query instead of using select case

ranu on 10/30/2010

like the innovative way of teaching ..new things..thanks

Jack Pines on 11/1/2010

Thanks for including the gotcha about the 8000 character (varchar-specific?) limit.

32DE12EB89 on 11/1/2010

It sounded like you were disturbed by the behavior of the select len(SPACE(8000)+'x') returning 0. Isn’t this the normal case of having a right trim on any LEN function? Doing select len(SPACE(7999)+'xy') returns 8000 to illustrate the 8000 character limit. Thanks for all the great information you share.

GParvathesam on 11/1/2010

-

Steve Culshaw on 11/4/2010

Great series looking at the different functions, especially the edge effects

Olu on 12/16/2010

Really good - I didn't know about the Replicate fn until now!