Post reply

CR/LF

  • 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,

  • July 1, 2005 at 5:18 am

    #570278

    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

  • July 1, 2005 at 5:21 am

    #570280

    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/

  • July 1, 2005 at 7:18 am

    #570299

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

    Mike

  • July 1, 2005 at 9:13 am

    #570376

    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.

  • July 1, 2005 at 11:26 am

    #570424

    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.

  • July 4, 2005 at 12:23 am

    #570583

    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/

  • July 4, 2005 at 5:00 am

    #570614

    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

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

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