Take value based on line break sql

I did notice the post code itself got line break so when we are giving '4' it is taking only first part. I would like to keep everything under postcode after 4th line break.

Please let me know

Many thanks in advance

Hi @srk ,

Please try the following solution.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, WorkAddress VARCHAR(MAX));  
INSERT INTO @tbl VALUES   
('89 Oliver''s Yard' + CHAR(13) +   
 '21 City Rd'+ CHAR(13) +   
 'London'+ CHAR(13) +   
 'EC1Y' + CHAR(13) + '1HP');  
-- DDL and sample data population, end  
  
DECLARE @separator CHAR(1) = CHAR(13);  
  
SELECT t.*  
 , c.value('(/root/r[1]/text())[1]', 'VARCHAR(30)') AS Address1  
 , c.value('(/root/r[2]/text())[1]', 'VARCHAR(30)') AS Street  
 , c.value('(/root/r[3]/text())[1]', 'VARCHAR(30)') AS City  
 , c.value('(/root/r[4]/text())[1]', 'VARCHAR(30)') + SPACE(1) +  
	c.value('(/root/r[5]/text())[1]', 'VARCHAR(30)') AS PostCode  
FROM @tbl AS t  
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
      REPLACE(WorkAddress, @separator, ']]></r><r><![CDATA[') +   
      ']]></r></root>' AS XML)) AS t1(c);  

Output

+----+---------------------------------------------+------------------+------------+--------+----------+  
| ID |                 WorkAddress                 |     Address1     |   Street   |  City  | POstCode |  
+----+---------------------------------------------+------------------+------------+--------+----------+  
|  1 | 89 Oliver's Yard 21 City Rd London EC1Y 1HP | 89 Oliver's Yard | 21 City Rd | London | EC1Y 1HP |  
+----+---------------------------------------------+------------------+------------+--------+----------+  

Hi，@srk

Welcome to Microsoft T-SQL Q&A Forum!

Try this:

create table #test  
 (  
 ID INT identity(1,1),  
 WorkAddress nvarchar(50)  
 )  
insert into #test values('89 Oliver''s Yard' + CHAR(13) + '21 City Rd'+ CHAR(13) + 'London'+ CHAR(13) +'EC1Y 1HP');  

 select * from #test  

 select   
 REVERSE(PARSENAME(REPLACE(REVERSE(WorkAddress ),  CHAR(13), '.'), 1)) AS [address1]  
    , REVERSE(PARSENAME(REPLACE(REVERSE(WorkAddress ),  CHAR(13), '.'), 2)) AS [street]  
    , REVERSE(PARSENAME(REPLACE(REVERSE(WorkAddress ),  CHAR(13), '.'), 3)) AS [city],  
    REVERSE(PARSENAME(REPLACE(REVERSE(WorkAddress ),  CHAR(13), '.'), 4)) AS [Postcode]  
 from #test  

]1
Best regards,
Bert Zhou

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our **Documentation** to enable e-mail notifications if you want to receive the related email notification for this thread.

 ;with mycte as (
SELECT id, '["'+ REPLACE(WorkAddress, CHAR(13), '","') + '"] ' jsCol
FROM test
)


select distinct id
,JSON_VALUE(jsCol, '$[0]') AS Address1 
,JSON_VALUE(jsCol, '$[1]') AS Street 
,JSON_VALUE(jsCol, '$[2]') AS City 
,JSON_VALUE(jsCol, '$[3]') AS Postcode 
FROM mycte
cross apply openjson(jsCol ) as j
 

Hi Bert,

Thanks for your reply ... it is not comma separated as its got line break between address1,street,city and postcode in workaddress column