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

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

Convert int to date

Hi,
i have a string representation like '200509'. I want to convert that to
"Septembre 2005". So i've split the two parts up with the left and right
function (now i've got 09 and 2005). Is there a way to say that the 09-part
must transform automatically to "Septembre". I'd rather not use and endless
iif structure. Setting the textbox to a Date type doesn't seem to work
either.
Thx and kind regards,
KoenYou might be able to do something with the SQL functions DATEPART and
DATENAME, I'll look into it and get back to you.
Koen wrote:
>Hi,
>i have a string representation like '200509'. I want to convert that to
>"Septembre 2005". So i've split the two parts up with the left and right
>function (now i've got 09 and 2005). Is there a way to say that the 09-part
>must transform automatically to "Septembre". I'd rather not use and endless
>iif structure. Setting the textbox to a Date type doesn't seem to work
>either.
>Thx and kind regards,
>Koen
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Alternatively, you could write a function to convert it using a switch case
statement in custom code.
I'll write it for you if you like, I don't have much to do at the moment!
Tabby Cool wrote:
>You might be able to do something with the SQL functions DATEPART and
>DATENAME, I'll look into it and get back to you.
>>Hi,
>[quoted text clipped - 7 lines]
>>Thx and kind regards,
>>Koen
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Do you need all of the month names to be in French?
I'll do them that way and you can always change it later if you want
something else.
Koen wrote:
>Hi,
>i have a string representation like '200509'. I want to convert that to
>"Septembre 2005". So i've split the two parts up with the left and right
>function (now i've got 09 and 2005). Is there a way to say that the 09-part
>must transform automatically to "Septembre". I'd rather not use and endless
>iif structure. Setting the textbox to a Date type doesn't seem to work
>either.
>Thx and kind regards,
>Koen
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Are all of your dates 21st century dates? Or do you have some from last
century?
Tabby Cool wrote:
>Do you need all of the month names to be in French?
>I'll do them that way and you can always change it later if you want
>something else.
>>Hi,
>[quoted text clipped - 7 lines]
>>Thx and kind regards,
>>Koen
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Is the value you are using actually a string or an int?
The title of the thread would imply it's an integer, but you said in your
post that it's a string.
I've nearly finished your function, but I need to know whether it's a string
or an integer.
Tabby Cool wrote:
>Do you need all of the month names to be in French?
>I'll do them that way and you can always change it later if you want
>something else.
>>Hi,
>[quoted text clipped - 7 lines]
>>Thx and kind regards,
>>Koen
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1