Reputation: 929
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: 65926
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
Reputation: 465
Try this. It's simpler:
SELECT DISTINCT name, location FROM object WHERE location NOT LIKE '[0-9]%';
Upvotes: 5
Reputation: 31
ISNUMERIC
should work. (will exclude 0 as well).
Sample code -
ISNUMERIC(SUBSTRING(location, 1, 1)) = 0
Upvotes: -1
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
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
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