Select Query | Select Entires That Don't Start With A Number - MySQL

Question

I need to select all entries that do not start with a number between 1-9.

Example Entries:

• 6300 Dog Lane
• Kitty Drive
• 500 Bird Chrest
• 800 Tire Road
• Johnson Ave
• Park Ave

So if I ran a query on the above I would expect:

• Kitty Drive
• Johnson Ave
• Park Ave

Table is called objects and the column is called location.

Something I tried:

SELECT DISTINCT name, location FROM object WHERE location NOT LIKE '1%' OR '2%' OR '3%' OR '4%' OR '5%' OR '6%' OR '7%' OR '8%' OR '9%';

Unfortunately, that is unsuccessful. If this is not possible please let me know and I will resort to modifying the data with Perl.

Thank You

Answer 1

Try this:

SELECT DISTINCT name, location FROM object
       WHERE substring(location, 1, 1)
                  NOT IN ('1','2','3','4','5','6','7','8','9');

or you have to add NOT LIKE before every number:

SELECT DISTINCT name, location FROM object
       WHERE location NOT LIKE '1%'
          OR location NOT LIKE '2%'
          ...

Answer 2

You can use the following stntax:

SELECT column FROM TABLE where  column NOT REGEXP '^[0-9]+$' ;


SELECT DISTINCT name, location FROM object
                WHERE location NOT REGEXP '^[0-9]+$' ;

Answer 3

Try this, its simpler:-

SELECT DISTINCT name, location FROM object WHERE location NOT LIKE '[0-9]%';