How to select all values that are not numeric across multiple columns in SQL Server?

Question

I have one table Prices

ID Price_1 Price_2 Price_3

P1 10      11      12
P2 13      14      15
P3 aa      16      bb
P4 19      cc      20

As you can see from above, some values from columns Price_1, Price_2 and Price_3 might not be numeric.

What I want is that first all find all those non-numeric values and then give an summary (concatenate all non-numeric values and columns for one id)

So for the above example, what I want is

ID   Bad_Columns      Bad_Values
P3   Price_1,Price_3  aa,bb
P4   Price_2          cc

How should i write this query?

Ok, if you want to have some kind of audit table with bad values for columns, then I strongly recommend that you do **not** concatenate those values, no point on doing that on your database. You could have that same table with one row per bad value per column. — Lamak, Mar 04 '13 at 15:57
@Lamak thanks for your advices. I just need to produce a summary table — Jackson Tale, Mar 04 '13 at 16:00

score 2 · Accepted Answer · answered Mar 04 '13 at 15:56

2

You could do something like this:

WITH CTE AS
(   SELECT  ID, Value, ColumnName
    FROM    Prices
            UNPIVOT
            (   Value
                FOR ColumnName IN ([Price_1], [Price_2], [Price_3])
            ) upvt
    WHERE   ISNUMERIC(Value) = 0
)
SELECT  ID,
        BadColumns = STUFF((SELECT  ', ' + ColumnName 
                            FROM    CTE
                            WHERE   CTE.ID = t.ID
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 2, ''),
        BadValues = STUFF(( SELECT  ', ' + Value 
                            FROM    CTE
                            WHERE   CTE.ID = t.ID
                            FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM    (SELECT DISTINCT ID FROM CTE) t

The first part UNPIVOTs your query to get columns as rows, then the second part will concatenate the "bad" rows into one column.

Example on SQL Fiddle

answered Mar 04 '13 at 15:56

GarethD

68,045
10
83
123

sorry, I have added bad_columns as well, how to adapt to that? – Jackson Tale Mar 04 '13 at 15:59
can I use `WITH...AS...` inside a trigger? I got error: `Incorrect syntax near 'CTE'.` – Jackson Tale Mar 04 '13 at 16:14
There is no problem using it inside a trigger, You need to ensure that the line before `WITH` is terminated with a semi-colon (or put a semi-colon before the `WITH`). – GarethD Mar 04 '13 at 16:19
after the last select, should again terminate? – Jackson Tale Mar 04 '13 at 16:24
Yes, although this should not make too much difference. – GarethD Mar 04 '13 at 16:26
ok, what if the `prices` table has an extra `name` column which is not unique and I want to include name column in the final summary table? – Jackson Tale Mar 04 '13 at 16:38

score 0 · Answer 2 · answered Mar 04 '13 at 15:57

You can use the following which will unpivot the data and then concatenate the values using FOR XML PATH:

;with cte as
(
  select id, col, data
  from
  (
    select t.id,
      c.col,
      case c.col
        when 'Price_1' then Price_1
        when 'Price_2' then Price_2
        when 'Price_3' then Price_3
      end as data
    from yourtable t
    cross join
    (
      select 'Price_1' as col
      union all select 'Price_2'
      union all select 'Price_3'
    ) c
  ) src
  where isnumeric(data) = 0
)
select distinct c1.id,
  stuff((select ', ' + col
         from cte c2
         where c1.id = c2.id
         FOR XML PATH (''))
          , 1, 1, '')  AS Bad_columns,
  stuff((select ', ' + data
         from cte c2
         where c1.id = c2.id
         FOR XML PATH (''))
          , 1, 1, '')  AS Bad_Values
from cte c1

See SQL Fiddle with Demo.

This can also be written using the unpivot function:

;with cte as
(
  select id, col, data
  from yourtable
  unpivot
  (
    data
    for col in ([Price_1], [Price_2], [Price_3])
  ) unpiv
  where isnumeric(data) = 0
)
select distinct c1.id,
  stuff((select ', ' + col
         from cte c2
         where c1.id = c2.id
         FOR XML PATH (''))
          , 1, 1, '')  AS Bad_columns,
  stuff((select ', ' + data
         from cte c2
         where c1.id = c2.id
         FOR XML PATH (''))
          , 1, 1, '')  AS Bad_Values
from cte c1

See SQL Fiddle with Demo. The result is:

| ID |       BAD_COLUMNS | BAD_VALUES |
---------------------------------------
| P3 |  Price_1, Price_3 |     aa, bb |
| P4 |           Price_2 |         cc |

score 0 · Answer 3 · answered Mar 04 '13 at 15:59

I would suggest:

select p.*
from ((select id, price_1 as price, 'price_1' as col) union all
      (select id, price_2 as price, 'price_2' as col) union all
      (select id, price_3 as price, 'price_3' as col)
     ) p
where is_numeric(price) = 0 and price is not null

If you actually want them in the format you have, just do:

select id,
       stuff((case when isnumeric(price_1) = 1 then ',Price_1' else '' end) +
             (case when isnumeric(price_2) = 1 then ',Price_2' else '' end) +
             (case when isnumeric(price_3) = 1 then ',Price_3' else '' end)
            ), 1, 1, '') as Bad_Columns,
       stuff((case when isnumeric(price_1) = 1 then ','+Price_1 else '' end) +
             (case when isnumeric(price_2) = 1 then ','+Price_2 else '' end) +
             (case when isnumeric(price_3) = 1 then ','+Price_3 else '' end)
            ), 1, 1, '') as Bad_Values
from p
where isnumeric(price_1) = 0 or isnumeric(price_2)= 0 or isnumeric(price_3) = 0

The reason that I'm not using unpivot is because that assumes that the column types are compatible. If you want to extend this to more columns, use Excel to generate the code.

How to select all values that are not numeric across multiple columns in SQL Server?

3 Answers3