use tempdb;goSELECT OrderNo , CHARINDEX('-',OrderNo,4) AS FindDash , LEFT(OrderNo,CHARINDEX('-',OrderNo,4)) AS NoSuffixFROM (SELECT 'SO-123456' AS OrderNoUNION ALLSELECT 'SO-123456-01'UNION ALLSELECT 'SO-123456-2'UNION ALLSELECT 'SO-123457'UNION ALLSELECT 'SO-123457-1'UNION ALLSELECT 'SO-123457-02'UNION ALLSELECT 'SO-123458') x

use tempdb;goSELECT OrderNo , CHARINDEX('-',OrderNo,4) AS FindDash , CASE WHEN LEFT(OrderNo,CHARINDEX('-',OrderNo,4)) ='' THEN OrderNo ELSE LEFT(OrderNo,CHARINDEX('-',OrderNo,4)) END AS NoSuffixFROM (SELECT 'SO-123456' AS OrderNoUNION ALLSELECT 'SO-123456-01'UNION ALLSELECT 'SO-123456-2'UNION ALLSELECT 'SO-123457'UNION ALLSELECT 'SO-123457-1'UNION ALLSELECT 'SO-123457-02'UNION ALLSELECT 'SO-123458') x

SELECT distinct CASE WHEN LEFT(OrderNo,CHARINDEX('-',OrderNo,4)) ='' THEN OrderNo ELSE substring(OrderNo,1,CHARINDEX('-',OrderNo,4)-1) END AS NoSuffixFROM (SELECT 'SO-123456' AS OrderNoUNION ALLSELECT 'SO-123456-01'UNION ALLSELECT 'SO-123456-2'UNION ALLSELECT 'SO-123457'UNION ALLSELECT 'SO-123457-1'UNION ALLSELECT 'SO-123457-02'UNION ALLSELECT 'SO-123458') x

select substring(OrderNo,1,9) from (SELECT 'SO-123456' AS OrderNoUNION ALLSELECT 'SO-123456-01'UNION ALLSELECT 'SO-123456-2'UNION ALLSELECT 'SO-123457'UNION ALLSELECT 'SO-123457-1'UNION ALLSELECT 'SO-123457-02'UNION ALLSELECT 'SO-123458') xgroup by substring(OrderNo,1,9) ;

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, NewOrderNoFROM SampleData aCROSS APPLY ( SELECT p1=CHARINDEX('-', OrderNo) ,s1=RIGHT(OrderNo, LEN(OrderNo)-CHARINDEX('-', OrderNo)) ) bCROSS APPLY( SELECT NewOrderNo=LEFT(OrderNo, p1+CHARINDEX('-', s1+'-')-1)) c;

SELECT DISTINCT LEFT(OrderNo, ISNULL(NULLIF(CHARINDEX('-',OrderNo,CHARINDEX('-',OrderNo)+1) - 1,-1),LEN(OrderNo)))FROM (SELECT 'SO-123456' AS OrderNoUNION ALLSELECT 'SO-123456-01'UNION ALLSELECT 'SO-123456-2'UNION ALLSELECT 'SO-123457'UNION ALLSELECT 'SO-123457-1'UNION ALLSELECT 'SO-123457-02'UNION ALLSELECT 'SO-123458') x

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 OrderNoINTO #TableFROM SampleData;UPDATE aSET OrderNo = NewOrderNoFROM #Table aCROSS APPLY ( SELECT p1=CHARINDEX('-', OrderNo) ,s1=RIGHT(OrderNo, LEN(OrderNo)-CHARINDEX('-', OrderNo)) ) bCROSS APPLY( SELECT NewOrderNo=LEFT(OrderNo, p1+CHARINDEX('-', s1+'-')-1)) c;SELECT *FROM #Table;GODROP TABLE #Table;