Converting alpha-numeric values

Hello, all. I have come across a dilemma working with SQL server 2005. My
company has a parts database. They want to be able to select a range of
parts based on the part number. Problem is that the part numbers are mixed
with non-numeric characters and special characters. Obviously convert will
not work in the scenario. An example part number:
123-304-10000A
Is there a way to get this included in a range search say from 100 to 200?
I wrote a proc that strips out the non-numeric characters but it is way too
slow with over 140000 parts in the table.
Any help would be appreciated.
Here is one way to strip out the non-numeric characters and to filter on the
numeric part only. In production replace the reference to the system table
master..spt_values with real utility table with numbers
(http://www.projectdmx.com/tsql/tblnumbers.aspx).
CREATE TABLE Parts (
part_no VARCHAR(14) PRIMARY KEY);
INSERT INTO Parts VALUES ('123-304-10000A');
INSERT INTO Parts VALUES ('AAA-BBB-00100A');
INSERT INTO Parts VALUES ('AAA-CCC-00150A');
WITH CleanParts (part_no, num_part_no)
AS
(SELECT part_no, CAST(
(SELECT SUBSTRING(part_no, n, 1)
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Nums(n)
WHERE n <= LEN(part_no)
AND SUBSTRING(part_no, n, 1) LIKE '[0-9]'
FOR XML PATH('')) AS BIGINT)
FROM Parts)
SELECT part_no
FROM CleanParts
WHERE num_part_no BETWEEN 100 AND 200;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
sqlsql