By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select *
into t
from (values ('LINE_NO=15^ORDER_NO=176572^RELEASE_NO=1^')) as v(key_ref)
1 rows affected
select t.*, s.orderno
from t outer apply
(select stuff(s.value, 1, 9, '') as orderno
from string_split(t.key_ref, '^') s
where s.value like 'ORDER_NO=%'
) s
| key_ref | orderno |
|---|---|
| LINE_NO=15^ORDER_NO=176572^RELEASE_NO=1^ | 176572 |