Split field on space or other characters

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.

Comments Closed.