Suppose we have some record as under
Data
------
9884556781 Smith <html><body>this is Smith's mobile number</body></html>
9887856781 Donald <html><body>this is Donald's mobile number</body></html>
45/78,KRRoad,Kanoji-345678 Debang <html><body>this is Debang's house</body></html>
We want to be in a relational way as under
MobileNumber OR Address Owner Name Details
----------------------- ---------- -----------
9884556781 Smith <html><body>this is Smith's mobile number</body></html>
9887856781 Donald <html><body>this is Donald's mobile number</body></html>
45/78,KRRoad,Kanoji-345678 Debang <html><body>this is Debang's house</body></html>
The below TSQL Script will do so
DECLARE @T TABLE(Data VARCHAR(100))
INSERT INTO @T VALUES('9884556781 Smith <html><body>this is Smith''s mobile number</body></html>')
INSERT INTO @T VALUES('9887856781 Donald <html><body>this is Donald''s mobile number</body></html>')
INSERT INTO @T VALUES('45/78,KRRoad,Kanoji-345678 Debang <html><body>this is Debang''s house</body></html>')
SELECT
[MobileNumber OR Address]=PARSENAME(BeforeHtmlContents, 2)
,[Owner Name]=PARSENAME(BeforeHtmlContents, 1)
,Details = HtmlContents
FROM
(SELECT
BeforeHtmlContents = REPLACE(RTRIM(SUBSTRING(Data,1,PATINDEX('%<%',Data)-1)),' ','.')
,HtmlContents = SUBSTRING(Data,PATINDEX('%<%',Data),LEN(Data))
FROM @T)X