See http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]

to get the code and explanation for use of the DelimitedSplit8K function.

;WITH SampleData (ID,PartNumber)

AS

(

SELECT 1,'BR643-0034-344-34' UNION ALL

SELECT 2,'AS54-54689' UNION ALL

SELECT 3,'A77360-3454-34456-4' UNION ALL

SELECT 4,'B7374-343-33-32-356-433-11-1'

)

SELECT

r.PartNumber

,LEFT(r.PartNumber,LEN(r.PartNumber)-LEN(r.Item)-1) AS NewPartNumber

FROM

(

SELECT

ID

,PartNumber

,ItemNumber

,Item

,(MAX(ItemNumber) OVER (PARTITION BY PartNumber)) AS MaxItem

FROM

SampleData AS s

CROSS APPLY

dbo.DelimitedSplit8K(s.PartNumber,'-') AS dsk

) r

WHERE

r.ItemNumber = r.MaxItem

ORDER BY

r.ID