[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Replacing LF in a SQL Select statement

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?

Thanks
0
jdr0606
Asked:
jdr0606
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
brandonvmooreCommented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now