Extract data from one column of SQL Server table
Basically, sql is not meant to parse text.  The database design is the issue here and it probably needs to be modified.  Another option is to return the string to the server and do the parsing there and then make another call to sql.

My answer is basically a copy of Andrew White's answer to this stackoverflow question.