Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the syntax.

I'm looking for something like:

SELECT 'A' + ('a' * 1000) + 'ha!'

To come up with "Aaaaaaaaaaaaaaaha!" (Well, much longer, of course.)

Is this possible in T-SQL? (Or am I thinking of some other language?) Also, are there any other techniques to generate large strings?

share|improve this question

1 Answer 1

up vote 12 down vote accepted

You can use REPLICATE:

SELECT 'A' + REPLICATE('a', 1000) + 'ha!';
share|improve this answer
1  
Note that you'll have to provide a (MAX) input character to generate strings larger than 8060 characters: SELECT REPLICATE(CAST('a' AS varchar(MAX)), 15000). Make sure to cast it to nvarchar(MAX) if necessary. –  Mark S. Rasmussen Aug 18 '11 at 14:34
1  
Thanks Mark. There are other potential issues with REPLICATE too, depending on how Richard will use it (e.g. it can behave differently when using length of varchar vs. char), which is why I hot-linked the REPLICATE keyword to point to the documentation, instead of trying to regurgitate all of the potential gotchas from the documentation here. –  Aaron Bertrand Aug 18 '11 at 14:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.