Castlewood
asked on
how to get all the records with 8-digit numbers
Hi, Experts,
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:
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:
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
By default, when you use the SQL Server import/export wizard to import from Excel, SQL Server appends a $ to the worksheet name when forming the table name. I have no idea why that default was chosen.
You can override that if you wish.
You can override that if you wish.
ASKER
Can you also tell me why the table name with a $ in the end after I imported an Excel table to SQL? For example, the table name is something like tmp_part$.
Thanks.