I needed to split a field that looks like this: 'MRLMCADE—01CAB101A' into 2 fields at the '—' mark. I could not count on the '—' always starting at the 9th character so I used the following:
LEFT( system_id , CHARINDEX( '—', system_id ) – 1 ) as Part_1
RIGHT( system_id , CHARINDEX( '—', REVERSE( system_id ) ) – 1 ) as Part_2
Now the hyphens could appear anywhere and the split would still work.