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
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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?
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