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
解决方案
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%’
…