Search code examples
sql-servert-sqlvariableswhile-loopreplicate

SQL REPLICATE() function not replicating and simply returning a single instance of the provided string when assigning to variable


I'm currently trying to solve an SQL programming challenge (building a triangular structure using asterisk characters). Example output below (3 rows);

* * * 
* *
*

I have written code which I believe should work (coming from some hobby C# experience, so I may be approaching this in the completely wrong way). However, my use of the REPLICATE() function appears to only output the provided character/string once, without actually replicating.

I have provided my current code here:

DECLARE @n INT = 3;
DECLARE @output NVARCHAR;
DECLARE @nextLine NVARCHAR;
DECLARE @char NVARCHAR = '* ';

WHILE @n > 0
BEGIN
    SET @nextLine = REPLICATE(@char, @n);
    SET @output = CONCAT(@output, @nextLine, CHAR(10));
    SET @n = @n - 1;
END

PRINT TRIM(@output);

Expected output:

* * * 
* *
*

Actual current output:

*

To clarify/re-iterate in a different way;

SELECT REPLICATE(@char, @n);
SET @output = REPLICATE(@char, @n);
SELECT @output;

The 1st SELECT above, returns the expected output (a string like '* * * * ....'), however, the 2nd SELECT - trying to SET that as a variable's value, only retains a single asterisk (''). Why?


Solution

  • Declare Length of the parameter @output and @nextLine.

    DECLARE @output NVARCHAR(50);
    DECLARE @nextLine NVARCHAR(50);