check this
Declare @string as nvarchar(max)
declare @Head as nvarchar(100)
declare @HeadDetail as nvarchar(500)
Declare @Host as nvarchar(500)
Declare @Connection as nvarchar(500)
Declare @Referer as nvarchar(500)
set @string=
'OPTIONS /Ads/PageTerms/GetTerms HTTP/1.1
Host: apps.developermedia.com
Connection: keep-alive
Cache-Control: max-age=0
Access-Control-Request-Method: POST
Origin: http://www.codeproject.com
User-Agent: Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML. like Gecko) Chrome/38.0.2125.111 Safari/537.36
Access-Control-Request-Headers: content-type
Accept: */*
Referer: http://www.codeproject.com/Articles/25600/Triggers-SQL-Server
Accept-Encoding: gzip.deflate.sdch
Accept-Language: en-US.en;q=0.8.ru;q=0.6
'
declare @charindexloc int,@charindexlocNext int,@lengthOfString int
select @charindexloc=CHARINDEX('Host:',@string)
select @lengthOfString=LEN(@string)
select @charindexlocNext=CHARINDEX(CHAR(10),Substring(@string,@charindexloc,@lengthOfString))
select SUBSTRING(@string,@charindexloc,@charindexlocNext) As Host
select @charindexloc=CHARINDEX('Referer:',@string)
select @lengthOfString=LEN(@string)
select @charindexlocNext=CHARINDEX(CHAR(10),Substring(@string,@charindexloc,@lengthOfString))
select SUBSTRING(@string,@charindexloc,@charindexlocNext) As Referer