SELECT COUNT（*）列中2个数字之间的位置

I am trying to count how many records I have in my column 'routeorder' that are between 2 numbers. The column has information like:

RouteOrder
ACC-001-01
ACC-001-01
ACC-001-01
ACC-001-01
ACC-015-18
ACC-015-18
ACC-015-18
ACC-015-18
ACC-015-19
ACC-015-19
ACC-015-19
ACC-015-19
ACC-015-19
ACC-016-01
ACC-016-01
ACC-016-01
ACC-017-19
ACC-017-19
ACC-017-19
ACC-017-19
ACC-017-19
ACC-017-19
ACC-017-19

I need to get how many records between 'ACC-001' and 'ACC-016' Is there a way to do it ? This is what I have so far:

SELECT COUNT(*) as areatotals FROM Customers WHERE SUBSTRING(routeorder,LENGTH(routeorder),-3) like 'ACC'

Thanks for the help.

Going from the example given on the official documentation, you should be able to that:

SELECT COUNT(*) as areatotals FROM Customers WHERE routeorder BETWEEN 'ACC-001' AND 'ACC-016';

This should trigger a string conversion as described here.

try this

SELECT count(*) counts,SUBSTRING(SUBSTRING(RouteOrder , 5),1,3 ) as str 
FROM Customers
GROUP BY str
HAVING str > 001 and str < 016

DEMO HERE

output:

   COUNTS   STR
    9       015

EDIT:

here more simpler and fits your needs if the first part is changing.

 SELECT count(*) counts,SUBSTRING(RouteOrder ,-6 ,3) as str 
 FROM Customers
 GROUP BY str
 HAVING str > 001 and str < 016

DEMO HERE

You dont even need to substring or typecast anything. the BETWEEN Operator is also working for strings: SELECT COUNT(*) FROM test WHERE RouteOrder BETWEEN "ACC-002" AND "ACC-007"

http://sqlfiddle.com/#!2/9614c/1

And if you want to include the Upperbound matches also, use SELECT COUNT(*) FROM test WHERE RouteOrder BETWEEN "ACC-002" AND "ACC-007" OR RouteOrder LIKE "ACC-007%"

(the like you need, because when comparing strings ACC-007-1 is technically NOT smaller or equal to ACC-007, which means, the between operator will exclude that)

http://sqlfiddle.com/#!2/9614c/2