TSQL Script to make unstructured record into relational record

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 479
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

Comments or Responses

Login to post response