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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 14 down vote accepted

You can use REPLICATE:

SELECT 'A' + REPLICATE('a', 1000) + 'ha!';
share|improve this answer
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
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


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.