MUPDATE
Updated 2023-10-19 15:44:52.417000
Syntax
SELECT [westclintech].[wct].[MUPDATE](
<@A, nvarchar(max),>
,<@start_row_A, int,>
,<@end_row_A, int,>
,<@start_col_A, int,>
,<@end_col_A, int,>
,<@EOperator, nvarchar(4000),>
,<@B, nvarchar(max),>
,<@start_row_B, int,>
,<@end_row_B, int,>
,<@start_col_B, int,>
,<@end_col_B, int,>)
Description
Use the scalar function MUPDATE to change the values in the string representation of a matrix or to perform element-wise operations on a matrix or some portion of a matrix. A new string representation is returned.
Arguments
@A
A string representation of the A matrix
@EOperator
Identifies the elementwise operation to be performed. The eligible values are '+', '-', '*', '/', and '='.
@end_row_B
The last row of @B to be included in the result. @end_row_B must be of a type int or of a type that implicitly converts to int.
@B
A string representation of the B matrix
@start_row_A
The first row of @A to be included in the result. @start_row_A must be of a type int or of a type that implicitly converts to int.
@end_col_B
The last column of @B to be included in the result. @end_col_B must be of a type int or of a type that implicitly converts to int.
@start_row_B
The first row of @B to be included in the result. @start_row_B must be of a type int or of a type that implicitly converts to int.
@start_col_B
The first column of @B to be included in the result. @start_col_Bmust be of a type int or of a type that implicitly converts to int.
@end_col_A
The last column of @A to be included in the result. @end_col_A must be of a type int or of a type that implicitly converts to int.
@start_col_A
The first column of @A to be included in the result. @start_col_A must be of a type int or of a type that implicitly converts to int.
@end_row_A
The last row of @A to be included in the result. @end_row_A must be of a type int or of a type that implicitly converts to int.
Return Type
nvarchar(max)
Remarks
The string representations of @A and/or @B 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-numeric 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.
If @B is NULL then the returned matrix is @A or a subset of @A.
If @A is NULL then the returned matrix is @B or a subset of @B.
If @start_row_A is NULL then @start_row_A equals 1.
If @start_col_A is NULL then @start_col_A equals 1.
If @start_row_B is NULL then @start_row_B equals 1.
If @start_col_B is NULL then @start_col_B equals 1.
If @end_row_A is NULL then @end_row_A equals the last row in in @A.
If @end_col_A is NULL then @end_col_A equals the last row in in @A.
If @end_row_B is NULL then @end_row_B equals the last row in in @B.
If @end_col_B is NULL then @end_col_B equals the last row in in @B.
Examples
Example #1
In this example we want to create a 3-by-3 matrix with each element set to the square root of 2.
SELECT wct.MUPDATE(wct.ZERO(3,3),NULL,NULL,NULL,NULL,'=',1.4142135623731,NULL,NULL,NULL,NULL) as M;
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--SELECT [0],[1],[2]
--FROM wct.MATRIX(wct.MUPDATE(wct.ZERO(3,3),NULL,NULL,NULL,NULL,'=',1.4142135623731,NULL,NULL,NULL,NULL))d
--PIVOT (MAX(ItemValue) FOR ColNum in ([0],[1],[2]))pvt
--ORDER BY RowNum;
This produces the following result.
{"columns":[{"field":"M"}],"rows":[{"M":"1.4142135623731,1.4142135623731,1.4142135623731;1.4142135623731,1.4142135623731,1.4142135623731;1.4142135623731,1.4142135623731,1.4142135623731"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1.4142135623731","1":"1.4142135623731","2":"1.4142135623731"},{"0":"1.4142135623731","1":"1.4142135623731","2":"1.4142135623731"},{"0":"1.4142135623731","1":"1.4142135623731","2":"1.4142135623731"}]}
Example #2
In this example we only want to update the matrix such that the lower right-hand corner is equal the square root of 2; in other words the first column and the first row will still contain zeroes.
SELECT wct.MUPDATE(wct.ZERO(3,3),2,3,2,3,'=',1.4142135623731,NULL,NULL,NULL,NULL) as M;
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--SELECT [0],[1],[2]
--FROM wct.MATRIX(wct.MUPDATE(wct.ZERO(3,3),2,3,2,3,'=',1.4142135623731,NULL,NULL,NULL,NULL))d
--PIVOT (MAX(ItemValue) FOR ColNum in ([0],[1],[2]))pvt
--ORDER BY RowNum;
This produces the following result.
{"columns":[{"field":"M"}],"rows":[{"M":"0,0,0;0,1.4142135623731,1.4142135623731;0,1.4142135623731,1.4142135623731"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"0","1":"0","2":"0"},{"0":"0","1":"1.4142135623731","2":"1.4142135623731"},{"0":"0","1":"1.4142135623731","2":"1.4142135623731"}]}
Example #3
In this example we add 2 to every value in the matrix @A.
DECLARE @A as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
DECLARE @C as varchar(max);
SET @C = wct.MUPDATE(@A,NULL,NULL,NULL,NULL,'+',2,NULL,NULL,NULL,NULL);
SELECT @C as C
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"C"}],"rows":[{"C":"3,3,3,3,3;3,4,6,10,18;3,5,11,29,83;3,6,18,66,258;3,7,27,127,627"}]}
Here are the results formatted as a table.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"3","1":"3","2":"3","3":"3","4":"3"},{"0":"3","1":"4","2":"6","3":"10","4":"18"},{"0":"3","1":"5","2":"11","3":"29","4":"83"},{"0":"3","1":"6","2":"18","3":"66","4":"258"},{"0":"3","1":"7","2":"27","3":"127","4":"627"}]}
Example #4
In this example we return the reciprocal of every element in the @A matrix.
DECLARE @A as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
DECLARE @C as varchar(max);
SET @C = wct.MUPDATE(1,NULL,NULL,NULL,NULL,'/',@A,NULL,NULL,NULL,NULL);
SELECT @C as C;
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"C"}],"rows":[{"C":"1,1,1,1,1;1,0.5,0.25,0.125,0.0625;1,0.333333333333333,0.111111111111111,0.037037037037037,0.0123456790123457;1,0.25,0.0625,0.015625,0.00390625;1,0.2,0.04,0.008,0.0016"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1","1":"1","2":"1","3":"1","4":"1"},{"0":"1","1":"0.5","2":"0.25","3":"0.125","4":"0.0625"},{"0":"1","1":"0.333333333333333","2":"0.111111111111111","3":"0.037037037037037","4":"0.0123456790123457"},{"0":"1","1":"0.25","2":"0.0625","3":"0.015625","4":"0.00390625"},{"0":"1","1":"0.2","2":"0.04","3":"0.008","4":"0.0016"}]}
Example #5
In this example we subtract the @B matrix from the @A matrix.
DECLARE @A as varchar(max) = wct.EYE(5,5);
DECLARE @B as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
SET @B = wct.MUPDATE(1,NULL,NULL,NULL,NULL,'/',@B,NULL,NULL,NULL,NULL);
DECLARE @C as varchar(max);
SET @C = wct.MUPDATE(@A,NULL,NULL,NULL,NULL,'-',@B,NULL,NULL,NULL,NULL);
SELECT @C as C
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"C"}],"rows":[{"C":"0,-1,-1,-1,-1;-1,0.5,-0.25,-0.125,-0.0625;-1,-0.333333333333333,0.888888888888889,-0.037037037037037,-0.0123456790123457;-1,-0.25,-0.0625,0.984375,-0.00390625;-1,-0.2,-0.04,-0.008,0.9984"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"0","1":"-1","2":"-1","3":"-1","4":"-1"},{"0":"-1","1":"0.5","2":"-0.25","3":"-0.125","4":"-0.0625"},{"0":"-1","1":"-0.333333333333333","2":"0.888888888888889","3":"-0.037037037037037","4":"-0.0123456790123457"},{"0":"-1","1":"-0.25","2":"-0.0625","3":"0.984375","4":"-0.00390625"},{"0":"-1","1":"-0.2","2":"-0.04","3":"-0.008","4":"0.9984"}]}
Example #6
In this example we subtract @B from @A excluding the first row and the first column.
DECLARE @A as varchar(max) = wct.EYE(5,5);
DECLARE @B as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
SET @B = wct.MUPDATE(1,NULL,NULL,NULL,NULL,'/',@B,NULL,NULL,NULL,NULL);
DECLARE @C as varchar(max);
SET @C = wct.MUPDATE(@A,2,5,2,5,'-',@B,2,5,2,5);
SELECT @C as C;
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"C"}],"rows":[{"C":"0.5,-0.25,-0.125,-0.0625;-0.333333333333333,0.888888888888889,-0.037037037037037,-0.0123456790123457;-0.25,-0.0625,0.984375,-0.00390625;-0.2,-0.04,-0.008,0.9984"}]}
Here are the results formatted as a matrix.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"0.5","1":"-0.25","2":"-0.125","3":"-0.0625"},{"0":"-0.333333333333333","1":"0.888888888888889","2":"-0.037037037037037","3":"-0.0123456790123457"},{"0":"-0.25","1":"-0.0625","2":"0.984375","3":"-0.00390625"},{"0":"-0.2","1":"-0.04","2":"-0.008","3":"0.9984"}]}
Example #7
In this example we subtract @B from @A, excluding the first row and the first column where we want to retain the original @A values.
DECLARE @A as varchar(max) = wct.EYE(5,5);
DECLARE @B as varchar(max) = '1,1,1,1,1;1,2,4,8,16;1,3,9,27,81;1,4,16,64,256;1,5,25,125,625';
SET @B = wct.MUPDATE(1,NULL,NULL,NULL,NULL,'/',@B,NULL,NULL,NULL,NULL);
DECLARE @C as varchar(max);
SET @C = wct.MUPDATE(@A,2,5,2,5,'=',wct.MUPDATE(@A,2,5,2,5,'-',@B,2,5,2,5),NULL,NULL,NULL,NULL);
SELECT @C as C;
--Only run this SQL to automatically PIVOT the results into the
--traditional row/column matrix presentation
--DECLARE @M as nvarchar(max) = N'SELECT @cols FROM wct.Matrix(@C) d PIVOT(MAX(ItemValue) FOR ColNum in (@cols))p ORDER BY RowNum';
--DECLARE @cols as nvarchar(max);
--SET @cols = (SELECT '[' + cast(ColNum as varchar(max)) + ']' FROM wct.MATRIX(@C) WHERE RowNum = 0 ORDER BY colnum FOR XML PATH(''));
--SET @cols = REPLACE(@cols,'][','],[');
--SET @M = REPLACE(REPLACE(@M,'@cols',@cols),'@C','''' + @C + '''');
--EXECUTE(@M);
This produces the following result.
{"columns":[{"field":"C"}],"rows":[{"C":"1,0,0,0,0;0,0.5,-0.25,-0.125,-0.0625;0,-0.333333333333333,0.888888888888889,-0.037037037037037,-0.0123456790123457;0,-0.25,-0.0625,0.984375,-0.00390625;0,-0.2,-0.04,-0.008,0.9984"}]}
Here are the results formatted as a table.
{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1","1":"0","2":"0","3":"0","4":"0"},{"0":"0","1":"0.5","2":"-0.25","3":"-0.125","4":"-0.0625"},{"0":"0","1":"-0.333333333333333","2":"0.888888888888889","3":"-0.037037037037037","4":"-0.0123456790123457"},{"0":"0","1":"-0.25","2":"-0.0625","3":"0.984375","4":"-0.00390625"},{"0":"0","1":"-0.2","2":"-0.04","3":"-0.008","4":"0.9984"}]}