Split a TEXT string at each carriage return

[javedi]

Hello,

I have searched the forum and the internet but have not found a simple solution to splitting a column with data type TEXT where there is a carriage return.

I'd like to use charindex, substring, len but don't know how to combine them to look for data in between the carriage returns (CR).

For example;

123 Kings Road (CR) Holborn (CR) London (CR) W1 1AA

split into columns;

Address1

Address2​

Address3​

Postcode​

123 Kings Road

Holborn

​

London​

W1 1AA

I'm looking for the most simple solution compliant with SQL Server 2008.

Any one have any idea's?

Thanks,
Javedi

 

[sabinUE]

;with aCTE
AS(	
	select CAST('123 Kings Road (CR) Holborn (CR) London (CR) W1 1AA' as TEXT) as txtField
)
,bCTE 
AS (
	select '<Address><ad>'+replace(CAST(txtField as VARCHAR(MAX)),'(CR)','</ad><ad>')+'</ad></Address>' as varcharField	
	FROM aCTE )
	
,cCTE 
AS (
	select CAST(varcharField as XML) as xmlField
	FROM bCTE )

select 
	t.u.value('.','varchar(500)') as [add]
	from cCTE as c
		cross apply c.xmlField.nodes('Address/ad') t(u)

output

123 Kings Road 
 Holborn 
 London 
 W1 1AA

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[javedi]

Hi SabinUE,

Just tested it and it really looks good. The text in my initial post was formatted incorrectly, i wanted it to be in columns not rows. Is this something easy to do and also will it work with a column from a table to split lots of addresses?

Thanks for your help.

Javedi

 

[sabinUE]

the final select look like this

select 
	t.u.value('ad[1]','varchar(500)') as [add]
	,t.u.value('ad[2]','varchar(500)') as [add]
	,t.u.value('ad[3]','varchar(500)') as [add]
	,t.u.value('ad[4]','varchar(500)') as [add]
	from cCTE as c
		cross apply c.xmlField.nodes('Address') t(u)

will it work with a column from a table to split lots of addresses?

Yes , will work ok

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[javedi]

Thanks, where do i include the table and column?

I tried the below but returned an error;

with aCTE
AS(
select CAST(corraddr as TEXT) from dbo.house as txtField
)
,bCTE

If there were more than 5 carriage returns what would happen?

Thanks again,
Javedi

 

[sabinUE]

[b];[/b] WITH

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[sabinUE]

; with aCTE
AS(	
select CAST(corraddr as TEXT) [b] as txtField [/b] from dbo.house 
)
,bCTE

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[javedi]

I tried that but it's put all data in first column.

and returned the error;

Msg 9421, Level 16, State 1, Line 1
XML parsing: line 1, character 29, illegal name character

 

[javedi]

Hi SabinUE,

I've re-written as;

; with aCTE
AS(
select corraddr as txtField
from dbo.househ
)
,bCTE
AS (
select '<Address><ad>'+replace(CAST(txtField as VARCHAR(MAX)),CHAR(13),'</ad><ad>')+'</ad></Address>' as varcharField
FROM aCTE )

,cCTE
AS (
select CAST(varcharField as XML) as xmlField
FROM bCTE )

select
t.u.value('ad[1]','varchar(500)') as [add1]
,t.u.value('ad[2]','varchar(500)') as [add2]
,t.u.value('ad[3]','varchar(500)') as [add3]
,t.u.value('ad[4]','varchar(500)') as [add4]
,t.u.value('ad[5]','varchar(500)') as [add5]
,t.u.value('ad[6]','varchar(500)') as [add6]
from cCTE as c
cross apply c.xmlField.nodes('Address') t(u)


I think the NULL values in the column are causing this error?

Javedi

 

[sabinUE]

I think not the null is the problem

XML parsing: line 1, character 29, illegal name character

this means that the

cCTE 
AS (
select CAST(varcharField as XML) as xmlField
FROM bCTE )

is trying to parse at xml

comment this and only display the replace stmt

select varcharField as xmlField

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[Olaf Doschke]

You could also use

http://madprops.org/blog/splitting-text-into-words-in-sql-revisited/

With SET @delims=CHR(13)+CHR(10) instead of ' ,:/-'.

Bye, Olaf.

 

[javedi]

,cCTE
AS (
select varcharField as xmlField
FROM bCTE )

I changed to the above statement and had the error below;

Msg 9506, Level 16, State 1, Line 1
The XMLDT method 'nodes' can only be invoked on columns of type xml.

 

[sabinUE]

yes , because , now , this is not a xml field

it;s only for a look to see how is the final result before parse it to xml

select varcharField as xmlField
FROM bCTE

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[javedi]

I'm getting the same error even after the change


; with aCTE
AS(
select corraddr as txtField
from dbo.househ
)
,bCTE
AS (
select '<Address><ad>'+replace(CAST(txtField as VARCHAR(MAX)),CHAR(13),'</ad><ad>')+'</ad></Address>' as varcharField
FROM aCTE )

,cCTE
AS (
select varcharField as xmlField
FROM bCTE)

select
t.u.value('ad[1]','varchar(500)') as [add1]
,t.u.value('ad[2]','varchar(500)') as [add2]
,t.u.value('ad[3]','varchar(500)') as [add3]
,t.u.value('ad[4]','varchar(500)') as [add4]
,t.u.value('ad[5]','varchar(500)') as [add5]
,t.u.value('ad[6]','varchar(500)') as [add6]
from cCTE as c
cross apply c.xmlField.nodes('Address') t(u)

 

[sabinUE]

select varcharField as xmlField
FROM bCTE

all the rows must look like this
<Address>

<ad>something</ad>​

</Address>


ignor for now,

select 
t.u.value('ad[1]','varchar(500)') as [add1]
,t.u.value('ad[2]','varchar(500)') as [add2]
,t.u.value('ad[3]','varchar(500)') as [add3]
,t.u.value('ad[4]','varchar(500)') as [add4]
,t.u.value('ad[5]','varchar(500)') as [add5]
,t.u.value('ad[6]','varchar(500)') as [add6]
from cCTE as c
cross apply c.xmlField.nodes('Address') t(u)

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[sabinUE]

you can inspire to this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin