How to check that a column values has string or digits values in mysql?

Question

I have mysql table with multiple columns, a column has string data type namely code and the values of the column might be digits only or mixed of string and digits e.g: one row value has 57677 and other rows column value like 2cskjf893 or 78732sdjfh.

mysql> select * from testuser;
+------+--------------+
| id   | code         |
+------+--------------+
|    1 | 232          |
|    2 | adfksa121dfk |
|    3 | 12sdf        |
|    4 | dasd231      |
|    5 | 897          |
+------+--------------+
5 rows in set (0.00 sec)

mysql> show create table testuser;
+----------+------------------------------------------------------------------    ---------------------------------------------------------------+
   | Table    | Create Table                                                                                                                        |
+----------+------------------------------------------------------------------    ---------------------------------------------------------------+
| testuser | CREATE TABLE `testuser` (
  `id` int(11) DEFAULT NULL,
  `code` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------    ---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

I would like to filter out only rows which has numeric value in code column or filter rows which has string values in code column.

JYoThI · Accepted Answer · 2016-04-26 03:54:05Z

3

try this

Mysql Query to fetch only integer values from a particular column

SELECT column FROM TABLE where column NOT REGEXP '^[0-9]+$' ;

Mysql Query to fetch only string values from a particular column

SELECT column FROM TABLE where  column REGEXP '^[0-9]+$' ;

edited Apr 26, 2016 at 3:54

answered Apr 25, 2016 at 11:19

JYoThI

12.1k1 gold badge13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bonatti Over a year ago

You might want to edit your answer, and separate the comments from the code, or even place several code lines, and text lines.

krishna Prasad Over a year ago

@jothi ISNUMERIC is not defined in MySql, getting this error: ERROR 1305 (42000): FUNCTION backend.ISNUMERIC does not exist, I am using following version of mysql: innodb_version | 5.6.26-74.0 version | 5.6.26-74.0-log got it from the query: SHOW VARIABLES LIKE "%version%";

krishna Prasad Over a year ago

Yes, got the result, you check it here: gist.github.com/krishnaiitd/…

Ankit Agrawal · Accepted Answer · 2016-04-25 11:20:51Z

3

To get digits

SELECT id,code 
FROM table_name
WHERE code REGEXP '^[0-9]+$';

To get string values

SELECT * 
FROM table_name
WHERE code REGEXP '^[a-zA-Z]+$'

edited Apr 25, 2016 at 11:20

answered Apr 25, 2016 at 11:04

Ankit Agrawal

2,4541 gold badge17 silver badges28 bronze badges

1 Comment

krishna Prasad Over a year ago

For getting numeric rows, your query work fine, you can check the query output here, for string its getting empty, I got the result by apply NOT operator in numeric regular expression.

FallAndLearn · Accepted Answer · 2016-04-25 11:07:36Z

1

Use regular expressions

SELECT * 
FROM myTable 
WHERE code REGEXP '^[0-9]+$';

This will filter out column values with all digits.

answered Apr 25, 2016 at 11:07

FallAndLearn

4,1351 gold badge20 silver badges24 bronze badges

1 Comment

krishna Prasad Over a year ago

YES, I got the result using regular expression.

vaso123 · Accepted Answer · 2016-04-25 11:05:47Z

0

You can use regular expressions in mysql too.

SELECT * FROM `table` WHERE `code` REGEXP '[0-9]';

Results will be the rows, where any number in the code column.

answered Apr 25, 2016 at 11:05

vaso123

12.4k4 gold badges37 silver badges68 bronze badges

1 Comment

krishna Prasad Over a year ago

No, your regular expression is not correct as per questions I have asked, check the output of your queries here

Thilina Sampath · Accepted Answer · 2016-04-26 09:30:25Z

0

Try this,

select id,code from table_name where code NOT REGEXP '^[0-9]+$'

Or,

select id,code from table_name where code like '%[^0-9]%'

edited Apr 26, 2016 at 9:30

answered Apr 25, 2016 at 11:43

Thilina Sampath

3,8017 gold badges43 silver badges67 bronze badges

3 Comments

krishna Prasad Over a year ago

First query: select id,code from testuser where code REGEXP '^[0-9]*'; gives the rows with values like 232 , adfksa121dfk, 12sdf, dasd231 897, I want to filter only the rows which has numeric values in code column or string value in code column, here the string examples are 12qwq, we121, 2w2 etc.

krishna Prasad Over a year ago

Second query return empty: mysql> select id,code from testuser where code like '%[^0-9]*%'; Empty set (0.00 sec)

krishna Prasad Over a year ago

I think your regular expression is not filtering only numeric value, you can check the output of your queries here: gist.github.com/krishnaiitd/… , I think it should '^[0-9]+$' for numeric rows.

Collectives™ on Stack Overflow

How to check that a column values has string or digits values in mysql?

5 Answers 5

3 Comments

1 Comment

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related