VARYING

SQL Server

Example

DECLARE @id TABLE (Id INT)
INSERT INTO @id VALUES(1),(2),(3)

DECLARE @value varchar(MAX)
SELECT @value = COALESCE(@value + ', ', '') + CAST(Id as varchar)
FROM @id

SELECT @value as Ids

Output

Ids
----
1, 2, 3

Explanation

In the example, a temporary table is declared and populated with values. A variable @value of type varchar(MAX) is used to concatenate the values in the column ‘Id’ of the @id table. The COALESCE function ensures an empty string is used instead of NULL for the first item in the list, and CAST is used to convert the integer value to a varchar type for string concatenation. The final SELECT statement displays the concatenated Ids.

Oracle

Example

DECLARE
   TYPE NumList IS VARRAY(10) OF NUMBER;
   arr_NumList NumList := NumList();
BEGIN
   arr_NumList.extend(5);
   arr_NumList(1) := 1;
   arr_NumList(2) := 2;
   arr_NumList(3) := 3;
   arr_NumList(4) := 4;
   arr_NumList(5) := 5;
   DBMS_OUTPUT.PUT_LINE(arr_NumList.COUNT);
END;

Output

5

Explanation

The example begins by declaring a variable array “NumList” with a maximum size of 10 and type NUMBER. It initializes an array “arr_NumList” of that type using “NumList()”. After extending the array size by 5, it assigns values to each index in the array. The output prints the count of the array’s elements, which is 5.