SQL - WHERE between on varchar column

Question

I have a table company with column customerID and I want every customer id from 80000 to 80999.

But the column customerID is varchar an some of them contain letters.

So, I must put customerID into ' ' and when I try this query:

select *
from company
where customerID between '80000' and '80999'

it returns also customerID 800 for example.

Any ideas how this issue can be solved?

score 0 · Answer 1 · edited May 23 '17 at 12:07

0

You could use regular expressions to format. Something like this should get you started Query to get only numbers from a string

edited May 23 '17 at 12:07

Community

answered Oct 13 '15 at 11:01

Daven.Geno

score 0 · Accepted Answer · answered Oct 13 '15 at 11:04

0

where customerID between '80000' and '80999'
  and character_length(customerID) = 5 -- only five digit IDs

answered Oct 13 '15 at 11:04

dnoeth

thank you - interesting solution, in my case the function would be LEN(customerID) = 5 – user1673665 Oct 13 '15 at 11:18

score 0 · Answer 3 · answered Apr 09 '16 at 10:03

0

If the CustomerID is an integer, which it should, you don't need the quotes around the numnbers. It should solve the problem:

select *
from company
where customerID between 80000 and 80999;

answered Apr 09 '16 at 10:03

3nrique0

3 Answers3