By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test(texi varchar(200));
insert into test values ('Order: T001.1<br />Rejection Reason: Meter Read Rejected: daily usage too high<br /><br /><a href=''http://********?ProfileId=111111&AccountId=22222&ProductId=11111&MeterId=11111''>Link to page</a>');
1 rows affected
SELECT
SUBSTRING(STUFF(texi, 1, CHARINDEX(':',texi,P3.pos), ''), 0,
CHARINDEX('<', STUFF(texi, 1, CHARINDEX(':',texi,P3.pos), ''))) as mystring
from test
cross apply (select (CHARINDEX(':',texi))) as P1(Pos)
cross apply (select (CHARINDEX(':',texi, P1.Pos+1))) as P2(Pos)
cross apply (select (CHARINDEX(':',texi, P2.Pos+1))) as P3(Pos)
| mystring |
|---|
| daily usage too high |