Mysql REGEXP with . and numbers only

Question

Consider a table category in a database, with column typeis. The datatype is varchar with values

         typeis
         ------
         2.5.1
         12
         1.1.1
         11
         letters12
         .........

I want to write a query that only returns records with "." and numbers from 0-9

For example

         2.5.1
         1.1.1

So far, I have

       select typeis from category where typeis
       not in 
       (select typeis from category where typeis REGEXP  '[^0-9 \.]+')
       and typeis in
       (select typeis from category where typeis REGEXP  '^[0-9]+[\.]')

which seems to work. The problem is that it takes over 3secs for just 1500 records. I would like to make it simpler and faster with just one REGEXP, instead of having nested select

Solution 1

Try: ^[0-9]+\.[0-9]+(\.[0-9]+)*

This should match things starting with a number(s) including a dot somewhere in the middle, and ending with numbers, and as many of these patterns as it would like.

Solution 2

This is pretty easy & powerful:

^([0-9]+\.*)+

The query-time issue could be caused by no indexing. Try to index typeis column - if it is possible create an index of its full length. For example if you've varchar(255) create the index of 255 length, like:

create index index_name on table_name (column_name(length))

Comments

[BlackVegetable]

I updated it right after the original posting. Try this one, as the original had a bug.

[BlackVegetable]

I don't think he wants to have a decimal by itself at the end, but I could be wrong.

[Zaffy]

@BlackVegetable I know, I edited this just before you posted this comment :)

[Admin]

3 sec became 0.00to 0.016 sec. Great

[BlackVegetable]

@tasos I'm glad it helped! Yes, nested SQL queries can sometimes be avoided and turn O(n) operations into O(n^2) or worse, depending on the layers of nesting.