Converting alpha-numeric values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NumbLock - 14 Mar 2008 19:35 GMT

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.

Reply to this Message

Plamen Ratchev - 14 Mar 2008 22:16 GMT

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

Reply to this Message