how to get all the records with 8-digit numbers
Posted on 2011-03-14
In my parts table there is a part_no field with mostly 8-digit numbers but some are text mixed. In VFP, I can easily use the following command to get all records with 8-digits part numbers:
SELECT * FROM t_parts WHERE BETWEEN(VAL(part_no), 10000000, 99999999) AND LEN(ALLTRIM(part_no))=8
But in SQL with the following command, I got an error saying "conversion failed when converting teh nvarchar value '110343BC' to data type int."
SELECT * FROM t_parts WHERE CONVERT(INT, part_no) BETWEEN 10000000 AND 99999999 AND LEN(part_no)=8
Can you tell me how to get all the records with 8-digit numbers in this situation? Tags: