IsNumeric and Case-when to count numeric values and change how it's displayed in results

Question

I'm trying to pull counts from a survey customers answer over the phone. The survey has 3 questions and the answers are sometimes numeric and sometimes words. I want to count the answers but need the any numeric values to be counted and displayed as NUMERIC_ENTRY. I've been trying 'IsNumeric' and 'Case when' but can't the results correct.

Sample Data:

Question | Answer  
Q1       | 12  
Q1       | 456  
Q1       | 8  
Q1       | DontKnow  
Q1       | TellMeHow  
Q2       | Yes  
Q3       | No

Sample Result:

Question | Asnwer          | Count  
Q1       |  NUMERIC_ENTRY  | 3  
Q1       | DontKnow        | 1  
Q1       | TellMeHow       | 1  
Q2       | Yes             | 1  
Q3       | No              | 1

The Example SQL:

select Question, Answer, count (*) from Survey
where Client = 'ABC_Company'   
group by Question, Answer

Hi and welcome to SO. Before we go any further you need to decide which DBMS you are using. mysql <> sql server. Then feel free to add the database specific tag for the DBMS you are using. — Sean Lange, Sep 21 '18 at 19:53

Barbaros Özhan · Answer 1 · 2018-09-21T20:36:14.973

Use TRY_PARSE function provided your version is SQL Server 2012 or above ( @Gordon is right using ISNUMERIC function might be problematic as in the below demonstration link ):

select q.Question, q.AnswerCount, count(q.AnswerCount) as count
  from
(
select Question, 
       (case when TRY_PARSE(Answer as int) is not null then 'NUMERIC_ENTRY'
            else Answer end) as AnswerCount
  from Survey   
 where Client = 'ABC_Company'   
 group by Question, Answer
) as q
group by q.Question, q.AnswerCount
order by q.Question, count desc

Rextester Demo

sticky bit · Answer 2 · 2018-09-21T20:12:17.020

You can use LIKE and a pattern, that matches any non digit character.

SELECT question,
       CASE
         WHEN answer = ''
               OR answer LIKE '%[^0-9]%'
           answer
         ELSE
           'NUMERIC_ENTRY'
       END answer,
       count(*)
       FROM survey
       WHERE client = 'ABC_Company'   
       GROUP BY question,
                CASE
                  WHEN answer = ''
                        OR answer LIKE '%[^0-9]%'
                    answer
                  ELSE
                    'NUMERIC_ENTRY'
                END;

(Assuming SQL Server because of the isnumeric() (which you can use too, but as it's known to produce funny results sometimes, I'd rather go for the LIKE).)

Another option since SQL Server 2012 is to use try_cast(), to check if the string can be converted into an integer.

SELECT question,
       CASE
         WHEN answer = ''
               OR try_cast(answer AS integer) IS NULL
           answer
         ELSE
           'NUMERIC_ENTRY'
       END answer,
       count(*)
       FROM survey
       WHERE client = 'ABC_Company'   
       GROUP BY question,
                CASE
                  WHEN answer = ''
                        OR try_cast(answer AS integer) IS NULL
                    answer
                  ELSE
                    'NUMERIC_ENTRY'
                END;

Madhur Bhaiya · Answer 3 · 2018-09-21T20:08:47.480

0

For MySQL: Taking idea from https://stackoverflow.com/a/5065007/2469308, you can check if a string is numeric or not, using If() function.

Try the following query:

select Question, 
       IF(CONCAT('',Answer * 1) = Answer, 'NUMERIC_ENTRY', Answer) AS Ans, 
       COUNT(*)
FROM Survey
where Client = 'ABC_Company'   
GROUP BY Question, Ans

edited Sep 21 '18 at 20:08

answered Sep 21 '18 at 20:01

Madhur Bhaiya

28,155
10
49
57

Gordon Linoff · Answer 4 · 2018-09-21T20:18:56.790

Don't use isnumeric(). You'll be surprised at some of the things considered numeric ('$', ',', and '3e2' for instance).

So, like is your friend in this case. By numeric, I assume you mean all digits. Because you want to aggregate by the results, you need to refer to the column twice. So, I'm going to suggest apply:

select s.question, v.answer_group, count(*)
from survey s cross apply
     (values (case when s.answer like '%[^0-9]%' then s.answer  -- has non-digit
                   else 'NUMERIC_ENTRY'
              end)
     ) v(answer_group)
group by s.question, v.answer_group
order by s.question, count(*) desc, v.answer_group;

Note that this treats the empty string as numeric. This is easily adjusted; your question is not clear about what to do in that case. I might suggest:

     (values (case when ltrim(rtrim(s.answer)) = '' or s.answer is null then 'BLANK_ENTRY'
                   when s.answer like '%[^0-9]%' then s.answer  -- has non-digit
                   else 'NUMERIC_ENTRY'
              end)
     ) v(answer_group)

I am really surprised that isnumeric() considers '$' as numeric, I use isnumeric() often and never notice any strange behavior, it always acted as intended. — Markov, Sep 21 '18 at 20:18

IsNumeric and Case-when to count numeric values and change how it's displayed in results

4 Answers4