Repeat the value using Replicate

This post is to repeat a specific value for specified number of times and assign that to a variable or if you have a column then do so.

This uses Replicate() function to repeat a string for max length of variable using sql_variant_property

The last statement LEN(@test) gets the lenght of the value assinged to the variable

DECLARE @test VARCHAR(255)
SET @test = ''
SET @test = REPLICATE('ab',CONVERT(INT,(select sql_variant_property(@test, 'MaxLength'))))
SELECT @test
SELECT LEN(@test)