The number of views of a numeric and non-numeric column value in one row in mysql

Question

I have a table test with column names that have both integer and non-integer values

+------------+ | names | +------------+ | 123 | | 123abc | | 89 | | dkkjdk | | dkdn | +------------+ 

I would like to display the number of integer and non-integer values ​​in one line like this

 integer_count non_integer_count 2 3 

I tried to print integer values ​​using this query

 select cast(names as signed) as int_val from test; 

But I got this result

 +---------+ | int_val | +---------+ | 123 | | 123 | | 89 | | 0 | | 0 | +---------+ 

The name field is the varchar (20) field.

Answer 1

Here is the solution

 SELECT COUNT(CASE WHEN not names REGEXP '^[0-9]+$' THEN 1 END) AS ALPHA, COUNT(CASE WHEN names REGEXP '^[0-9]+$' THEN 1 END) AS DIGIT FROM test; 

Output

 +-------+-------+ | ALPHA | DIGIT | +-------+-------+ | 3 | 2 | +-------+-------+ 

Answer 2

Use the sum of the condition based on the regular expression:

 select sum(names rlike '^[0-9]+$') integer_count, sum(names not rlike '^[0-9]+$') non_integer_count from test 

See SQLFiddle

Some explanation:

• rlike is a regular version like
• regex ^[0-9]+$ matches if the whole value is one or more digits. Note the ^ and $ , without which it will check the digit (s) somewhere in the value
• using sum(condition) calculates how many times the condition was true, because (in mysql) true is 1 and false is 0

Answer 3

You can use 10 calls for replace to get a name without numbers. Then you can calculate the number of digits by comparing the length of the name and the name without numbers:

 select names , length(names) - length(non_digit) as digit_chars , length(non_digit) as non_digit_chars from ( select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( names, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') as non_digit , names from YourTable ) as SubQueryAlias 

Example in SQL Fiddle.