MySQL SELECT only non alpha numbers

Question

I have a VARCHAR type field that can contains both numeric (222) or alpha-number (CPO13) values.

I want to select only values that are numeric? I see in example how to choose non-numeric but I want to filter out those with letters of the alphabet?

If you know how to choose non-numeric values, just add `NOT` to the `WHERE` condition and it will filter them out. — Barmar, Jul 11 '18 at 20:17

Nadim Younes · Accepted Answer · 2018-07-11T20:57:24.203

1

You can try using a Regular Expression that filters for values with both numeric and alphabetical characters . For example:

SELECT * FROM DATA WHERE FIELD REGEXP NOT '[A-z].[0-9]';

mySQL allows you to use regular expression as a filter !

This should select out for values like CPO13. If you want to practice regular expressions this is a great website to test whether you have the right regex. https://regex101.com/

Hope this helps !

edited Jul 11 '18 at 20:57

answered Jul 11 '18 at 20:14

Nadim Younes

800
1
6
11

You need to put the regexp in quotes. – Barmar Jul 11 '18 at 20:15
Yes, sorry I just edited it, i forgot to include them. Thank You – Nadim Younes Jul 11 '18 at 20:18
The question also says he wants to exclude alphabetic, not return them. – Barmar Jul 11 '18 at 20:19

MySQL SELECT only non alpha numbers

1 Answers1