I have four parameters, any of which may or may not contain a value (they may also all be NULL). The parameters need to be assessed in their numbered order.
DECLARE @val1 nvarchar(40) = 'Hello';
DECLARE @val2 nvarchar(40) = 'Treacle';
DECLARE @val3 nvarchar(40) = NULL;
DECLARE @val4 nvarchar(40) = 'Rhubarb';
I somehow need to find the "nth" non-null value. COALESCE is fine for determining the 1st non-null value, but is there a function or simple way of determining the nth?
Ideally, COALESCE would have an index parameter which, based on the above values, would return 'Rhubarb' if n were set to 3:
DECLARE @nth tinyint = 3;
SELECT COALESCE(@val1, @val2, @val3, @val4, @nth);
Can anyone please suggest a solution? This doesn't seem to be as straightforward as initially thought!
SwePeso's solution at the following link has been the best help for this.
January 10, 2020 at 8:54 pm
If something isn't a set, it often helps to turn it into a set. Here's an example:
DECLARE @val1 nvarchar(40) = 'Hello';
DECLARE @val2 nvarchar(40) = 'Treacle';
DECLARE @val3 nvarchar(40) = NULL;
DECLARE @val4 nvarchar(40) = 'Rhubarb';
DECLARE @nth tinyint = 3;
SELECT TOP 1 up.val
FROM (SELECT 1 AS rownum, @val1 AS val UNION ALL SELECT 2 AS rownum, @val2 AS val UNION ALL SELECT 3 AS rownum, @val3 AS val UNION ALL SELECT 4 AS rownum, @val4 AS val) up
WHERE up.rownum >= @nth
AND val IS NOT NULL;
January 10, 2020 at 9:26 pm
Great, thanks!
January 11, 2020 at 6:50 am
Here is another option
DECLARE @val1 nvarchar(40) = 'Hello';
DECLARE @val2 nvarchar(40) = 'Treacle';
DECLARE @val3 nvarchar(40) = NULL;
DECLARE @val4 nvarchar(40) = 'Rhubarb';
DECLARE @nth int = 2;
WITH cteNonNullData AS (
SELECT Pos = ROW_NUMBER() OVER(ORDER BY src.SearchOrder), src.Val
FROM (
VALUES ( 1, @val1)
, ( 2, @val2)
, ( 3, @val3)
, ( 4, @val4)
) AS src(SearchOrder, Val)
WHERE src.Val IS NOT NULL
)
SELECT Val
FROM cteNonNullData
WHERE Pos = @nth;
January 11, 2020 at 6:52 am
If something isn't a set, it often helps to turn it into a set. Here's an example:
DECLARE @val1 nvarchar(40) = 'Hello';
DECLARE @val2 nvarchar(40) = 'Treacle';
DECLARE @val3 nvarchar(40) = NULL;
DECLARE @val4 nvarchar(40) = 'Rhubarb';
DECLARE @nth tinyint = 3;
SELECT TOP 1 up.val
FROM (SELECT 1 AS rownum, @val1 AS val UNION ALL SELECT 2 AS rownum, @val2 AS val UNION ALL SELECT 3 AS rownum, @val3 AS val UNION ALL SELECT 4 AS rownum, @val4 AS val) up
WHERE up.rownum >= @nth
AND val IS NOT NULL;
This code needs an ORDER BY in order to guarantee that the nth record is found. As it stands, it will find a random value where the ordinal is >=@nth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply