MINDEX
Updated: 31 January 2012
Use the scalar function MINDEX to return the intersection of a row and a column from the string representation of a matrix.
MINDEX expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons.
Syntax
SELECT [wctMath].[wct].[MINDEX](
<@Matrix, nvarchar(max),>
,<@m, int,>
,<@n, int,>)
Arguments
@Matrix
a string representation of a matrix.
@m
The row number. Row number is a 1-based index.
@n
The column number. Column number is a 1-based index.
Return Types
[float]
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, use the NMatrix2String or the NMatrix2String_q function.
· @m must be greater than or equal to 1.
· If @m is greater than the number of rows an error message will be generated.
· To determine the number of rows in the matrix, use the MROWS function.
· @n must be greater than or equal to 1.
· If @m is greater than the number of rows 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 element in fifth row, fifth column, which we can refer to as A(5,5).
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.MINDEX(@A, 5, 5) as [A(5,5)]
This produces the following result.
A(5,5)
----------------------
-6
The matrix does not have to be assigned to a variable before passed into the MINDEX function; the string can be passed in directly.
SELECT wct.MINDEX('-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, 5) as [A(5,5)]
This produces the following result.
A(5,5)
----------------------
-6