I picked up this parsename trick on here and have been loving it for ad hoc queries.

with data(col) as (

SELECT '0222~Banana' UNION ALL

SELECT '0333~Carrot' UNION ALL

SELECT '0444~Danish' UNION ALL

SELECT '0555~Eclair'

)

select parsename(replace(col, '~', '.'), 2) as 'Left',

parsename(replace(col, '~', '.'), 1) as 'Right'

from data

where col like '%~%'