Solved

how to get all the records with 8-digit numbers

Posted on 2011-03-14
3
472 Views
Last Modified: 2013-11-10
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:
0
Comment
Question by:Castlewood
  • 2
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 35131891
SELECT *
FROM t_parts
WHERE part_no LIKE '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
0
 

Author Comment

by:Castlewood
ID: 35132697
Thank you for the prompt reply. That works.
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.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 35132752
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question