how to select numbers (integers) only?

jrahma

Hi,

I have employee_number field which has employee_numbers like this:

12
434
ABC345
QWR-567

I want to know how can I just select the numeric values to perform a MAX on it?

so ABC345 and QWR-567 will not be considered in the SELECT

Derokorian

where column REGEX '^[0-9]+$'

if you are using mysql.

laserlight

Out of curiosity, but why do you want to do this? Perhaps you should have two columns instead?

johanafm

If you need to use aggregate functions against parts of a column, your data is not normalized.

"ABC123" may be atomic, if it contains no parts with specific meaning, or may be considered atomic if you never need to make use of such parts. An example of this is street and street number. If you need to perform computations against the street numbers, then street and street number together is compound data. If you only ever use it to ship ordered goods, it may be considered atomic data.

The answer is (most likely) to normalize your data.

Also note that you may already use max directly on your employment number. Just order is assigned to numbers, it is assigned to character sets. This is called collation. I'm guessing max would would return QWR-567 for any collation.

Weedpacket

It would also be interesting to know why employee numbers come in so many different formats.

If different formats indicate different types of employee then it's a case of denormalised data as laserlight and johanafm suggest; there should be a less obfuscated way of distinguishing them (assuming that distinguishing them is necessary, but that's implied by the original question).

If on the other hand the different formats don't indicate anything about the employee then it's not clear what significance can be attached to the maximum employee number of a particular format.

jrahma

I tried this:

SELECT employee_number FROM employees WHERE employee_number REGEXP ('[0-9]');

it's working excellent and I getting these numbers

7, 8, 9, 11, 1.222, 12, 13, 9, 15, 9, 9, 9

BUT....

when I run:

SELECT MAX(employee_number) FROM employees WHERE employee_number REGEXP ('[0-9]');

I get 9 where i should get 15.

why is that?

Derokorian

Probably because its stored as a string, you'll need to cast it to an int first inside the MAX call. Have you consider the others' points about normalizing your data better such that you only have numbers when you want numbers?