Replacing LF in a SQL Select statement

Posted on 2009-04-15
Last Modified: 2012-05-06
I have a text file that I generate daily for a bank using a SQL select statement.

Periodically one of the address or description fields in one of the SQL tables has an imbedded Line Feed or return in it and forces the text file to be rejected by the bank.

Is there a way to replace LF or CR with a SQL function if found in a table field when running a select?

Question by:jdr0606
    LVL 142

    Accepted Solution

    this should do:
    UPDATE yourtable
      SET yourfield = REPLACE(yourfield, chr(13), ' ')
     WHERE yourfield like '%' + chr(13) + '%' 
    UPDATE yourtable
      SET yourfield = REPLACE(yourfield, chr(10), ' ')
     WHERE yourfield like '%' + chr(10) + '%'

    Open in new window

    LVL 5

    Assisted Solution

    That will update your table, but if you don't want to edit your table you could use the same function in your actual SELECT statement

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now