MySQL - Select only non - numeric values from varchar column

Question

create table mixedvalues (value varchar(50));

insert into mixedvalues values ('100');
insert into mixedvalues values ('ABC100');
insert into mixedvalues values ('200');
insert into mixedvalues values ('EFEA200');
insert into mixedvalues values ('300');
insert into mixedvalues values ('AAFASF300');
insert into mixedvalues values ('400');
insert into mixedvalues values ('AERG400');
insert into mixedvalues values ('500');
insert into mixedvalues values ('AGE500');

i want to select only non-numeric(alpha numeric) values, i.e ABC100,EFEA200,AAFASF300,AERG400,AGE500.

i have the code for selecting numeric values, i.e 100,200....

SELECT * 
FROM mixedvalues 
WHERE value REGEXP '^[0-9]+$';

Please help me,iam a beginner.

Do the letters A, B, and C always appear in each non-numeric record? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 21, 2016 at 10:31
@TimBiegeleisen no, it maynot be always ABC, any random alphabet — Shubham Ringne
– Shubham Ringne, Commented Oct 21, 2016 at 10:33

Tim Biegeleisen · Accepted Answer · 2016-10-21 10:52:04Z

33

The regex [a-zA-Z] should only fire true if a value contains at least one letter.

SELECT * 
FROM mixedvalues 
WHERE value REGEXP '[a-zA-Z]';   -- or REGEXP '[[:alpha:]]'

edited Oct 21, 2016 at 10:52

answered Oct 21, 2016 at 10:38

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

Cyril Jacquart Over a year ago

"non numeric" does not mean only containing letters. You also could have other chars...

Tim Biegeleisen Over a year ago

@CyrilJacquart The OP mentioned i want to select only non-numeric(alpha numeric) values ... I interpret this, along with the sample data provided, that all inputs would be strictly alphanumeric.

HoldOffHunger · Accepted Answer · 2020-02-17 20:07:28Z

9

The REGEXP '^[^0-9]+$' selects all non numeric characters

SELECT state
FROM `enquiry`
GROUP BY state
HAVING state REGEXP '^[^0-9]+$'

edited Feb 17, 2020 at 20:07

HoldOffHunger

21.3k11 gold badges123 silver badges146 bronze badges

answered Apr 11, 2018 at 7:01

sonofkrish

1011 silver badge4 bronze badges

3 Comments

ftrotter Over a year ago

This did not seem to catch special characters for some reason....

Krunoslav Djakovic Over a year ago

This will select only the ones that have non numeric at start. Use '[^0-9]' if you want to search for non-numeric anywhere in string

ヴァルガスアルド Over a year ago

I believe this is a better solution since it will work with any non numeric value, not only alphabet. Thanks

Collectives™ on Stack Overflow

MySQL - Select only non - numeric values from varchar column

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related