Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

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:
0
Castlewood
Asked:
Castlewood
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
CastlewoodAuthor Commented:
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
 
Patrick MatthewsCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now