CODEWITHSUNDEEP
mysql
Shubham Ringne
Shubham Ringne

Reputation: 433

MySQL - Select only non - numeric values from varchar column

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.

Upvotes: 28

Views: 21362

Answers (2)

sonofkrish
sonofkrish

Reputation: 91

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

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

Upvotes: 8

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

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:]]'

Upvotes: 31

Related Questions