MCOLUMN

Updated 2023-10-19 14:05:09.337000

Syntax

SELECT [westclintech].[wct].[MCOLUMN](
  <@Matrix, nvarchar(max),>
 ,<@n, int,>)

Description

Use the scalar function MCOLUMN to return a column from the string representation of a matrix. MCOLUMN expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons.

Arguments

@Matrix

a string representation of a matrix.

@n

The column number. Column number is a 1-based index.

Return Type

nvarchar(max)

Remarks

The string representations of @Matrix must only contain numbers, commas (to separate the columns), and semi-colons to separate the rows.

Consecutive commas will generate an error.

Consecutive semi-colons will generate an error.

Non-numeric data between commas will generate an error

Non-number data between semi-colons will generate an error

To convert non-normalized data to a string format, use the Matrix2String or the Matrix2String_q function.

To convert normalized data to a string format, us the NMatrix2String or the NMatrix2String_q function.

@n must be greater than or equal to 1.

If @n is greater than the number of columns an error message will be generated.

To determine the number of columns in the matrix, use the MCOLS function.

Examples

Let’s assume that we had the following matrix, A, and we want to return the fifth column.

A = [-79,-45,9,9,-91,-5;68,46,9,81,-61,35;83,-25,80,-67,-22,-38;77,40,-24,69,73,-20;-17,-72,-9,-72,-6,-34;64,-47,48,-54,18,11;-4,-36,7,-56,-34,-3;-41,90,78,-43,38,64;-60,-85,-31,-83,-96,-36;-40,31,-93,-62,64,10]

We could enter the following SQL to perform the calculation.

DECLARE @A as varchar(max);

SET @A

    = '-79,-45,9,9,-91,-5;68,46,9,81,-61,35;83,-25,80,-67,-22,-38;77,40,-24,69,73,

              -20;-17,-72,-9,-72,-6,-34;64,-47,48,-54,18,11;-4,-36,7,-56,-34,-3;

              -41,90,78,-43,38,64;-60,-85,-31,-83,-96,-36;-40,31,-93,-62,64,10';

SELECT wct.MCOLUMN(@A, 5) as [Column];

This produces the following result.

{"columns":[{"field":"Column"}],"rows":[{"Column":"-91;-61;-22;73;-6;18;-34;38;-96;64"}]}

Which will produce exactly the same result. The matrix does not have to be assigned to a variable before passed into the MCOLUMN function; the string can be passed in directly.

SELECT wct.MCOLUMN(

                      '-79,-45,9,9,-91,-5;68,46,9,81,-61,35;83,-25,80,-67,-22,-38;

                                77,40,-24,69,73,-20;-17,-72,-9,-72,-6,-34;64,-47,

                                48,-54,18,11;-4,-36,7,-56,-34,-3;-41,90,78,-43,38,

                                64;-60,-85,-31,-83,-96,-36;-40,31,-93,-62,64,10',

                      5

                  ) as [Column];

This produces the following result.

{"columns":[{"field":"Column"}],"rows":[{"Column":"-91;-61;-22;73;-6;18;-34;38;-96;64"}]}