CR/LF

Question

CR/LF

TriggerMe

SSChampion

Points: 11752
More actions
July 1, 2005 at 4:29 am

#165146

Is there a way to detect CR/LF's in a string?
For example;
'This is
a test message'
I need it to return 'This is a test message' all in one line.
Can this be done in SQL. BOL has nothing on it.
Kindest Regards,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I would research BOL for PATINDEX and CHARINDEX along with REPLACE. I believe you will want to look for CHAR(13). If that is not the correct ASCII value you can play with it. See below example for output with CHAR(13)

SELECT CHAR(13) + 'A'

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

SELECT REPLACE(@string, '

', '')

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 3

Hi formating is best i performed on the client rather than the server.

Mike

Ray M SSC-Insane Points: 21093 More actions · Answer 4

I agree formatting is a presentation layer function.

there are a few things to consider.

1. Don't let data get into your database this way. The application should commit data to database they it wants it to be retrieved.

2. If this data is from an old system, or from a bug, then fix it once. Don't make your select statements modify the data on select.

3. Formatting in sql server wastes precious resources when you have alot of activity. Its easier to scale web servers than it is to scale a sql server.

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Both CHAR(13) and CHAR(10) can cause this problem.

I have a function along with a table that will prevent that information from getting into your database if you would like a copy.

I wasn't born stupid - I had to study.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 6

I do not agree completely here:

1. Don't let data get into your database this way. The application should commit data to database they it wants it to be retrieved.

The database should be in control of the data stored in it, not the application. The app should store the data in the way that the database wants it to be stored, or rather the database should enforce rules that keeps that data that way.

2. If this data is from an old system, or from a bug, then fix it once. Don't make your select statements modify the data on select.

And what if there is later a change in how the app wants to handle the data? Would the data now once again be seen as "from an old system"? And furthermore if a new system wants to use the database (and wants it formatted differently), should the data now be changed again? Which system takes precedence?

3. Formatting in sql server wastes precious resources when you have alot of activity. Its easier to scale web servers than it is to scale a sql server.

I completely agree with this!

To summarize, the data in the database should be stored in the way that the database design says it should be. This design should be based on the business rules that apply to the data. Applications that needs the data to be differently formatted should transform the data (client-side preferably), and the database should enforce rules to make sure that new data is stored in the way the design specifies.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 7

From the original post we do not know if the database has been rationalized. IF you would like to read Dr. Codd's rules for normalizing a db please check this site. http://www.frick-cpa.com/ss7/Theory_RelationalDB.asp#Codd as to the question at hand I agree with Chris, I was just pointing out that to conserve resources presentation should be performed on the clinet side.

HTH

Mike