14

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?

Community
  • 1
Richard
  • 6,393
  • 8
  • 42
  • 62

1 Answers1

22

You can use REPLICATE:

SELECT 'A' + REPLICATE('a', 1000) + 'ha!';

If you need to go beyond 8,000 bytes (4,000 characters for varchar, or 8,000 characters for nvarchar), you'll need to manually CONVERT as Mark suggested in the comment below:

SELECT  'X' + REPLICATE(CONVERT(varchar(max), 'Y'),    9000) + 'Z';
SELECT N'X' + REPLICATE(CONVERT(nvarchar(max), N''), 5000) + N'Z';
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • 5
    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