dwain.c (3/5/2014)

---

This could be done in a possibly more general fashion using DelimitedSplit8K but if you've only got 1 or 2 hyphens, you can also do it like this:

WITH SampleData (OrderNo) AS

(

SELECT 'SO-123456' AS OrderNo

UNION ALL SELECT 'SO-123456-01'

UNION ALL SELECT 'SO-123456-2'

UNION ALL SELECT 'SO-123457'

UNION ALL SELECT 'SO-123457-1'

UNION ALL SELECT 'SO-123457-02'

UNION ALL SELECT 'SO-123458'

)

SELECT OrderNo, NewOrderNo

FROM SampleData a

CROSS APPLY

(

SELECT p1=CHARINDEX('-', OrderNo)

,s1=RIGHT(OrderNo, LEN(OrderNo)-CHARINDEX('-', OrderNo))

) b

CROSS APPLY

(

SELECT NewOrderNo=LEFT(OrderNo, p1+CHARINDEX('-', s1+'-')-1)

) c;

Edit: Note that this also works if there are more than two characters ahead of the first hyphen.