CODEWITHSUNDEEP
mysqlsqlselect
Kirs Kringle
Kirs Kringle

Reputation: 929

Select query or select entries that don't start with a number - MySQL

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

Example Entries:

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

The 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. Is this possible? If no, I will resort to modifying the data with Perl.

Upvotes: 15

Views: 65927

Answers (6)

Milan
Milan

Reputation: 71

Try this for SQL Server:

select column_name
from table
where substring(column_name,1,1) not in (1,2,3,4,5,6,7,8,9)

Upvotes: 0

Soham Banerjee
Soham Banerjee

Reputation: 465

Try this. It's simpler:

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

Upvotes: 5

Shawn
Shawn

Reputation: 31

ISNUMERIC should work. (will exclude 0 as well).

Sample code -

ISNUMERIC(SUBSTRING(location, 1, 1)) = 0

Upvotes: -1

Tushar
Tushar

Reputation: 3643

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]+$' ;

Upvotes: 11

Jens
Jens

Reputation: 69515

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%'
          ...

Upvotes: 31

Rick James
Rick James

Reputation: 142560

What you "tried" needed to have AND instead of OR. Also, DISTINCT is unnecessary.

If you have

INDEX(location)

this would probably be faster than any of the other answers:

( SELECT name, location FROM object
    WHERE location < '1'
) UNION ALL
( SELECT name, location FROM object
    WHERE location >= CHAR(ORD('9' + 1)) )

This technique only works for contiguous ranges of initial letters, such as 1..9.

A somewhat related question: Should I perform regex filtering in MySQL or PHP? -- it asks about fetching rows starting with 1..9 instead of the opposite.

Upvotes: 0

Related Questions