Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Split a TEXT string at each carriage return

Status
Not open for further replies.

javedi

Technical User
Apr 30, 2007
196
GB
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


 
SQL:
;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
SQL:
123 Kings Road 
 Holborn 
 London 
 W1 1AA

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
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
 
the final select look like this
SQL:
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
 
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
 
SQL:
[b];[/b] WITH


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
SQL:
; 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
 
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
 
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
 
I think not the null is the problem

XML parsing: line 1, character 29, illegal name character
this means that the
SQL:
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
SQL:
select varcharField as xmlField






Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
,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.


 
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
 
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)
 
select varcharField as xmlField
FROM bCTE

all the rows must look like this
<Address>
<ad>something</ad>​
</Address>


ignor for now,
SQL:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top