Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server back-substitution function


Updated: 31 January 2012

Use the scalar function BKSUB to return a solution to the equation A*x=b, when A is an upper-triangular matrix.
BKSUB expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons.
SELECT [wctMath].[wct].[BKSUB](
  <@Matrix1, nvarchar(max),>
 ,<@Matrix2, nvarchar(max),>)
a string representation of an upper-triangular matrix.
a string representation of a vector having the same number of rows as @Matrix1.
Return Types
·         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.
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 = [4,-1,2,3;0,-2,7,-4;0,0,6,5;0,0,0,3]
y = [20;-7;4;6]
We could enter the following SQL to perform the calculation.
DECLARE @A as varchar(max)
DECLARE @y as varchar(max)
SET @A = '4,-1,2,3;0,-2,7,-4;0,0,6,5;0,0,0,3'
SET @y = '20;-7;4;6'
SELECT wct.BKSUB(@A, @y) as x
This produces the following result.
The matrices do not have to assigned variables before passed into the BKSUB function; the string can be passed in directly.
SELECT wct.BKSUB('4,-1,2,3;0,-2,7,-4;0,0,6,5;0,0,0,3', '20;-7;4;6') as x
This produces the following result.
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 4,-1,2,3,20 UNION ALL
      SELECT 0,-2,7,-4,-7 UNION ALL
      SELECT 0,0,6,5,4 UNION ALL
      SELECT 0,0,0,3,6
      ) A(xo,x1,x2,x3,y)
/* Do the back substitution */
SELECT wct.BKSUB(wct.MATRIX2STRING('#A','xo,x1,x2,x3','',NULL), wct.MATRIX2STRING('#A','y','',NULL)) as x
This produces the following result.
If we wanted to return the matrix product as a normalized table, we can use the table-valued function MATRIX to do that.
FROM wct.MATRIX((SELECT wct.BKSUB('4,-1,2,3;0,-2,7,-4;0,0,6,5;0,0,0,3', '20;-7;4;6')))
     RowNum      ColNum              ItemValue
----------- ----------- ----------------------
          0           0                      3
          1           0                     -4
          2           0                     -1
          3           0                      2

Copyright 2008-2023 Westclintech LLC         Privacy Policy        Terms of Service