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?


You can use REPLICATE:

SELECT 'A' + REPLICATE('a', 1000) + 'ha!';
  • 4
    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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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