I have a field called Defects which contain strings like the following. The string makeup is always the same.
Scrap : Part Assembly : Surface Defects : Scratch
Scrap : Part Assembly : Components : Wrong Components
Scrap : Part Assembly : Other : Change Over
Repair : Punching : Surface Damages : Crack
What I would like to do is create a view that splits the string by the : and have them displayed as individual fields.
I started of using CharIndex to find the : and then use left and the charindex to strip the first part out, but I felt doing it this way would involve a lot of LEFTs and RIGHTs to split up the whole string, is there an easier way to accomplish this?