Search code examples
mysqldistinct

Get unique values from MySql

In my table are values like: 14, 15, 14, R14, R15, R15C, R14C,....

With current distinct mode I get: 14, R14, R14C, R15, R15C,...

But what I want is only unique numbers like this: 14, 15,...

To get rid of letters and get unique numbers only?

My current code:

SELECT DISTINCT diameter FROM tires WHERE category = '$cat' ORDER BY diameter ASC
Solution 
  • SELECT DISTINCT 0 + REGEXP_REPLACE(diameter, '^[[:alpha:]]*', '') diameter
FROM tires;

    Regular expression removes all letter chars from the beginning, then the value is implicitly converted to numeric by adding zero (by fact - trailing letters are removed).

    fiddle

    I forget to tell that I have decimal values like R22,5 - RoX

    Replace comma with dot before or after REGEXP_REPLACE(), but before adding zero. For example,

    SELECT DISTINCT 0 + REGEXP_REPLACE(REPLACE(diameter, ',', '.'), '^[[:alpha:]]*', '') diameter
FROM tires;

    fiddle

    PS. I'd recommend to add generated column to the table structure. See example.