-2

Here is a example of a table:

    ID        Value1       Value2      Value3         
-----------------------------------------------------
1             1            2           NA
2             NA           3           NA
3             2            5           2
4             NA           NA          1

What is a query to replace NAs in column 'Value1' e.g. by 1, NAs in column 'Value2' by 3 and NAs in column 'Value3' by 2?

I think I'm looking for an equivalent to ISNULL function, but I don't find anything.

Thanks for your help in advance!!

  • 3
    What is the logic behind this pattern? I'm not seeing it, unless it's min - median - max. – Aaron Bertrand Mar 28 at 17:56
  • you're right. The columns contain time series characteristica as std, min, max, linear modulated slope and I wish to give them a different 'default' value – sqlNewie Mar 28 at 18:12
  • I don't want to select the IDs as in the query, but replace the NA values in the database, where the table is stored.. – sqlNewie Mar 28 at 18:24
0

The below query will replace the NA value in each column with the AVG value of non-NA rows in the data set. It uses a CTE and the UNION and PIVOT clauses to produce the 3 replacement values as a data set which is applied across the original table and CASE statements replace the original 'NA' values:

WITH CTE AS (
    SELECT [Value1], [Value2], [Value3]
    FROM
    (
        SELECT 'Value1' AS [Col],
            AVG(CAST(Value1 AS INT)) AS AvgValue
        FROM ValueData
        WHERE Value1 <> 'NA'
        UNION
        SELECT 'Value2' AS [Col],
            AVG(CAST(Value2 AS INT)) AS AvgValue
        FROM ValueData
        WHERE Value2 <> 'NA'
        UNION
        SELECT 'Value3' AS [Col],
            AVG(CAST(Value3 AS INT)) AS AvgValue
        FROM ValueData
        WHERE Value3 <> 'NA'
    ) src
    PIVOT
    (
        MAX([AvgValue]) FOR [Col] IN ([Value1], [Value2], [Value3])
    ) pvt
)

SELECT ID,
    CASE
        WHEN v.Value1 = 'NA' THEN c.Value1
        ELSE v.Value1
    END AS Value1,
    CASE
        WHEN v.Value2 = 'NA' THEN c.Value2
        ELSE v.Value2
    END AS Value2,
    CASE
        WHEN v.Value3 = 'NA' THEN c.Value3
        ELSE v.Value3
    END AS Value3
FROM ValueData v
CROSS APPLY CTE c

Results:

ID  Value1  Value2  Value3
--------------------------
1   1       2       1
2   1       3       1
3   2       5       2
4   1       3       1

I tested this against a dataset of 180,000 records and runtime was only 1 second so performance is not likely to be an issue.

I created a fake dataset using this code for testing:

CREATE TABLE ValueData (
    ID INT IDENTITY,
    Value1 VARCHAR(10),
    Value2 VARCHAR(10),
    Value3 VARCHAR(10)
)
GO

DECLARE @Val1 INT,
    @Val2 INT,
    @Val3 INT

SET @Val1 = CAST(ROUND(RAND()*3, 0) AS INT)
SET @Val2 = CAST(ROUND(RAND()*3, 0) AS INT)
SET @Val3 = CAST(ROUND(RAND()*3, 0) AS INT)

INSERT INTO ValueData (Value1, Value2, Value3)
SELECT 
    CASE
        WHEN @Val1 = 0 THEN 'NA'
        ELSE CAST(@Val1 AS VARCHAR(10))
    END AS Value1,
    CASE
        WHEN @Val2 = 0 THEN 'NA'
        ELSE CAST(@Val2 * (CAST(ROUND(RAND()*3, 0) AS INT) + 1) AS VARCHAR(10))
    END AS Value2,
    CASE
        WHEN @Val3 = 0 THEN 'NA'
        ELSE CAST(@Val3 / ((CAST(ROUND(RAND()*3, 0) AS INT) + 1)*2)+1 AS VARCHAR(10))
    END AS Value3
GO 180000
  • Thank you for the detailed help! Actually this helps me a lot! – sqlNewie Mar 29 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.