If i could ignore it then great if not i would just filter it out in the where clause once in a table.

The getdomain is a function see below

create function...

Final peice will be something like this

select

dbo.GetDomain(t.test,'|',0) as col2,

dbo.GetDomain(t.test,'|',1) as col3,

dbo.GetDomain(t.test,'|',2) as col4

from #test...

The sample of data im using is test data, i wouldnt get hung up about the quality of the data. I've amended the data you highlighted to explain the data...