CODEWITHSUNDEEP
mysqlsql
ChrisRockGM
ChrisRockGM

Reputation: 438

How do I filter out SQL rows where numbers range?

I have a table with a login column that has the text FTO and then a sequence of numbers following it (i.e. FTO3210 or FTO1002). I have a query that says SELECT * FROM tablename. I am trying to filter it so that it does not SELECT any row that has a login value ranging from FTO1000 to FTO1010.

Upvotes: 1

Views: 1105

Answers (3)

S Vinesh
S Vinesh

Reputation: 539

select * from table where login NOT BETWEEN 'FTO1000' AND 'FTO1010'

Upvotes: 1

Farrokh
Farrokh

Reputation: 1167

SELECT * FRM [Table] WHERE [Login] NOT BETWEEN 'FTO1000' AND 'FTO1010'

or

SELECT * FROM [Table] WHERE CAST(REPLACE([Login],'FTO','') AS INT) NOT BETWEEN 1000 AND 1010

Upvotes: 2

Ross Presser
Ross Presser

Reputation: 6255

SQLFiddle:

CREATE TABLE X ( 
  V VARCHAR(100) NOT NULL 
  );
INSERT X (V) VALUES ('FTO3210');
INSERT X (V) VALUES ('FTO1002');

SELECT V FROM X WHERE NOT SUBSTR(V,4,4) BETWEEN '1000' and '1010';

Upvotes: 1

Related Questions