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
  • 1
  • 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
  • 181,950
  • 28
  • 405
  • 624