SQL Reference - VARYING

VARYING

VARYING is a keyword in SQL used to define a column of a table as a variable-length character string. The VARYING character size allows the storage of character strings of varying lengths, conserving memory space by only using up as much space as required for each entry, as opposed to a fixed-length character string.

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.

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

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.