Menu
I need support in my sql query code. I have to replace null value in a column with not-null value from the next row.
as a example we can use this code:
declare @value table (r# int, value varchar(15)) insert into @value ( r#, value ) values (1, NULL ) , (2, 'January'), (3, 'February' ), (4, NULL ), (5, 'March' ), (6, NULL ), (7, Null ), (8, 'December' ), (9, Null ), (10, Null ), (11, Null ), (12, 'November' ), (13, Null ) select * from @value When I use lead function I get this value but it does not work with NULLs. What I need is to get:
1 January 2 January 3 February 4 March 5 March 6 December 7 December 8 December 9 November 10 November 11 November 12 November 13 NULL Bu from my query :
SELECT r#, value ,case when value is null then Lead(value) OVER ( order by r# asc) else value end as RESULT FROM @value order by r# I have:
918
The ISNULL Function is a built-in function to replace nulls with specified replacement values. To use this function, all you need to do is pass the column name in the first parameter and in the second parameter pass the value with which you want to replace the null value.
We can replace NULL values with a specific value using the SQL Server ISNULL Function. The syntax for the SQL ISNULL function is as follow. The SQL Server ISNULL function returns the replacement value if the first parameter expression evaluates to NULL.
Output: Step 5: Insert 10 rows into the WORKER table. Query: INSERT INTO WORKER VALUES('SAM','ONTARIO',NULL); INSERT INTO WORKER VALUES('TIM',NULL,56); INSERT INTO WORKER VALUES(NULL,'CAIRO',43); INSERT INTO WORKER VALUES(NULL,'MUMBAI',NULL); INSERT INTO WORKER VALUES(NULL,NULL,NULL);
Next approach may help. You need additional APPLY operator to find the first record with not NULL value:
T-SQL:
SELECT v1.[r#], COALESCE(v1.[value], v2.[value]) AS [value] FROM @value v1 OUTER APPLY ( SELECT TOP 1 [Value] FROM @value WHERE (v1.[r#] < [r#]) AND [value] IS NOT NULL ) v2 Output:
r# value 1 January 2 January 3 February 4 March 5 March 6 December 7 December 8 December 9 November 10 November 11 November 12 November 13 NULL If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
