Normalize data issue.

Friend has his part as PartNum. He manufactures replacement parts, and he
wants to be able to search on the OEM # and come to his part #.
PartNum Description
ABC123 800100-1 -2 -3 -5 -9 -11 -12.
Final output in my cross-reference table should look like:
ABC123 800100-1
ABC123 800100-2
ABC123 800100-3
ABC123 800100-5
ABC123 800100-9
ABC123 800100-11
ABC123 800100-12
ABC234 800101-1
ABC234 800101-2
ABC234 800101-3
Can that extraction be done via T-SQL, or will I have to do it in a .NET
job?
TIA
__Stephen>> Can that extraction be done via T-SQL, or will I have to do it in a .NET
One common trick in t-SQL is to use a table/view/derived table/CTE with
sequentially incrementing numbers. There are several ways you can generate
one, search the google archives of this newsgroup to find some or post back.
Once you have such a table, assuming the string format is consistent, the
cleanup can be done like:
SELECT p_num,
prefix + '-' +
SUBSTRING(value, n, CHARINDEX('-', value + '-', n) - n)
FROM ( SELECT PartNum, LEFT( descr, CHARINDEX( '-', descr ) - 1 ),
REPLACE( RIGHT( descr, LEN( descr) -
CHARINDEX( '-', descr )) , SPACE(1), '' )
FROM tbl ) T ( p_num, prefix, value ),
Nbrs
WHERE SUBSTRING( '-' + value, n, 1 ) = '-'
AND LEN(value) + 1 > n;
Here Nbr is the table of numbers with column name n and tbl is the table
where you have the original data.
Anith|||Thanks.
__Stephen
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23t$YdwEVGHA.6112@.TK2MSFTNGP10.phx.gbl...
> One common trick in t-SQL is to use a table/view/derived table/CTE with
> sequentially incrementing numbers. There are several ways you can generate
> one, search the google archives of this newsgroup to find some or post
> back.
> Once you have such a table, assuming the string format is consistent, the
> cleanup can be done like:
> SELECT p_num,
> prefix + '-' +
> SUBSTRING(value, n, CHARINDEX('-', value + '-', n) - n)
> FROM ( SELECT PartNum, LEFT( descr, CHARINDEX( '-', descr ) - 1 ),
> REPLACE( RIGHT( descr, LEN( descr) -
> CHARINDEX( '-', descr )) , SPACE(1), '' )
> FROM tbl ) T ( p_num, prefix, value ),
> Nbrs
> WHERE SUBSTRING( '-' + value, n, 1 ) = '-'
> AND LEN(value) + 1 > n;
> Here Nbr is the table of numbers with column name n and tbl is the table
> where you have the original data.
> --
> Anith
>