# SQL Server COLUMN function

MCOLUMN

Updated: 31 January 2012

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.
Syntax
SELECT [wctMath].[wct].[MCOLUMN](
<@Matrix, nvarchar(max),>
,<@n, int,>)
Arguments
@Matrix
a string representation of a matrix.
@n
The column number. Column number is a 1-based index.
Return Types
[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.
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.
Column
----------------------------------
-91;-61;-22;73;-6;18;-34;38;-96;64