FWDSUB
Updated: 31 January 2012
Use the scalar function FWDSUB to return a solution to the equation A*x=b, when A is a lower-triangular matrix.
FWDSUB expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons.
Syntax
SELECT [wctMath].[wct].[FWDSUB](
<@Matrix1, nvarchar(max),>
,<@Matrix2, nvarchar(max),>)
Arguments
@Matrix1
a string representation of a lower-triangular matrix.
@Matrix2
a string representation of a vector having the same number of rows as @Matrix1.
Return Types
[nvarchar](max)
Remarks
· The number of rows in @Matrix1 must equal the number of rows in @Matrix2.
· The string representations of @Matrix1 and @Matrix2 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.
· To convert the string result to a table, us the table-valued function MATRIX.
Examples
Let’s assume that we had the following matrices A and y, and we want to calculate the vector x such that A*x=y.
A = [3,0,0,0;-1,1,0,0;3,-2,-1,0;1,-2,6,2]
y = [5;6;4;2]
We could enter the following SQL to perform the calculation.
DECLARE @A as varchar(max)
DECLARE @y as varchar(max)
SET @A = '3,0,0,0;-1,1,0,0;3,-2,-1,0;1,-2,6,2'
SET @y = '5;6;4;2'
SELECT wct.FWDSUB(@A, @y) as x
This produces the following result.
x
--------------------------------------------------------------------
1.66666666666667;7.66666666666667;-14.3333333333333;50.8333333333333
Which will produce exactly the same result.
The matrices do not have to assigned variables before passed into the FWDSUB function; the string can be passed in directly.
SELECT wct.FWDSUB('3,0,0,0;-1,1,0,0;3,-2,-1,0;1,-2,6,2', '5;6;4;2') as x
This produces the following result.
x
--------------------------------------------------------------------
1.66666666666667;7.66666666666667;-14.3333333333333;50.8333333333333
In this example, the matrix values are stored on a table in the database and are converted to a string value using the MATRIX2STRING function.
/* Put matrices into a table */
SELECT *
INTO #A
FROM (
SELECT 3,0,0,0,5 UNION ALL
SELECT -1,1,0,0,6 UNION ALL
SELECT 3,-2,-1,0,4 UNION ALL
SELECT 1,-2,6,2,2
) A(xo,x1,x2,x3,y)
/* Do the forward substitution */
SELECT wct.FWDSUB(wct.MATRIX2STRING('#A','xo,x1,x2,x3','',NULL), wct.MATRIX2STRING('#A','y','',NULL)) as x
This produces the following result.
x
--------------------------------------------------------------------
1.66666666666667;7.66666666666667;-14.3333333333333;50.8333333333333
If we wanted to return the matrix product as a normalized table, we can use the table-valued function MATRIX to do that.
SELECT *
FROM wct.MATRIX((SELECT wct.FWDSUB('3,0,0,0;-1,1,0,0;3,-2,-1,0;1,-2,6,2', '5;6;4;2')))
RowNum ColNum ItemValue
----------- ----------- ----------------------
0 0 1.66666666666667
1 0 7.66666666666667
2 0 -14.3333333333333
3 0 50.8333333333333