SQL Server 2016 has (finally) a built-in string split function:
STRING_SPLIT ( string , separator )
That is great, however, if you are using an earlier version, many people end up writing something that loops through the string and wherever the separator shows up, the substring is copied to a table, which is returned later.
Here's an alternative... replace the separator with XML element tags and then use the built-in XML functionality to return the delimited elements. In theory, this may be slower than a tight string loop, but in practice, the XML libraries are extremely efficient, and the REPLACE function is also highly optimized. It certainly takes less code than the other approach.