Take value based on line break sql

Question

Hi there,

I got the address field separated with line break between the sentence. At the moment it is storing like below in the table

89 Oliver's Yard
21 City Rd
London
EC1Y 1HP

I want to split them to different columns

like below

    select distinct   
	  
	   
  LEFT(workaddress, CHARINDEX(char(13),workaddress) - 1) address1  
  ,''  as Street  
  ,''City  
  ,reverse(SUBSTRING(reverse(isnull(workaddress,' ')), 1, charindex(char(13),reverse( isnull(workaddress,' ')), 1))) as PostCode  
    
  from TempAddress  

Can anyone help me on this?

Answer 1

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 |  
+----+---------------------------------------------+------------------+------------+--------+----------+  

Answer 2

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.

Answer 3

 ;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
 

Answer 4

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

Answer 5

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