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?

0

1 Answer 1

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';
2
  • 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. Aug 18, 2011 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. Aug 18, 2011 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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