Find Carriage return - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find Carriage return

Author

Topic

cykophysh
Starting Member

24 Posts

Posted - 2007-03-06 : 11:45:01

Hi Guys I am writing the following query

Select  daydesc as description ,Features,
        adfull, askprice,   bedrooms as bedroom,rooms,
	floorarea as sqm, (pADDRESS1 + ''+  PADDRESS2+ ''+PADDRESS2+         ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS  ,directions 
		
from  dbo.PROPERTY as Property
	where property_id = @id
		for xml auto,elements

It works as expected However, what I would like to achieve is that within the Features(text) field there are carriage return statements, where I would like to extract as some like


<Features>
<feature>blah</feature>
<feature>blah blah</feature>
</Features>

Is this possible ?
I thought about using something like

CHARINDEX(features,CHAR(13)+CHAR(10))as feature

but a little unsure of how to go about it

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-06 : 12:14:30

I don't understand "what I would like to achieve is that within the Features(text) field there are carriage return statements, where I would like to extract as some".
What do you want to actually do with the carriage returns?

cykophysh
Starting Member

24 Posts

Posted - 2007-03-06 : 12:34:30

Sorry What I mean is
The Features Field is a text Field. The Legacy application has allowed the user to input Data allowing a carriage return statement which the Text looks like
<features>Deceptively spacious family home
Quality cream fitted kitchen
</features>

I would like to Return the Field like this
<features><feature>Deceptively spacious family home</feature>
<feature>Quality cream fitted kitchen</feature>
</features>

Kind Regards,
Gary

<A href="http://www.threenineconsulting.com" target="_blank" >My Website</a> || <a href="http://threenineconsulting.com/forum/blogs/cykophysh/default.aspx" targer="_blank">My Blog</a>

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-06 : 16:50:44

Good thing I checked, not what I thought you wanted at all

This will do it, just put an opening tag at the beginning, a closing tag at the end and then replace all carriage returns with an open and a close. Much easier with SQL Server 2005 because you can use varchar(max) instead of text for the column type and varchar(max) lets you use string functions like replace. You should change your column to varchar(max) if it isn't already.

select daydesc as description ,
	cast('<feature>' 
	+ replace(Features, char(13)+char(10), '</feature><feature>') 
	+ '</feature>' as xml) AS Features,
        adfull, askprice,   bedrooms as bedroom,rooms,
	floorarea as sqm, (pADDRESS1 + ''+  PADDRESS2+ ''+PADDRESS2
	+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS  ,directions 
 
from dbo.PROPERTY as Property
	where property_id = @id
		for xml auto,elements

If Features is text instead of varchar(max), you'll get an error saying you cannot use the text data type with the replace function. As I said I'd recommend that you change the column, but you could also do this in the query

select daydesc as description ,
	cast('<feature>' 
	+ replace(cast(Features as varchar(max)), char(13)+char(10), '</feature><feature>') 
	+ '</feature>' as xml) AS Features,
        adfull, askprice,   bedrooms as bedroom,rooms,
	floorarea as sqm, (pADDRESS1 + ''+  PADDRESS2+ ''+PADDRESS2
	+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS  ,directions 
 
from dbo.PROPERTY as Property
	where property_id = @id
		for xml auto,elements

cykophysh
Starting Member

24 Posts

Posted - 2007-03-07 : 05:21:27

Thank you so much, that is fantastic and it works like a charm!!
I can now use this idea elsewhere

Kind Regards,
Gary

<A href="http://www.threenineconsulting.com" target="_blank" >My Website</a> || <a href="http://threenineconsulting.com/forum/blogs/cykophysh/default.aspx" targer="_blank">My Blog</a>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources