Re: Remove the last carriage return and line feed from sql text field

(whitej77777@xxxxxxxxx) writes:
I am trying to write a user defined function that will allow me to
strip off the last carriage return and line feed from a text field.
We have address fields stored in a text field for our ERP system and
some of them have an extra carriage return and line feed at the end of
them. This causes havoc when we sync between our ERP system and CRM
system. If anyone knows a way to solve this problem the help would be
appreciated.

Examples:
Existing Text field with CR:

1234 Blah Street<CR>
Suite 2345<CR>

Corrected Text field:

1234 Blah Street<CR>
Suitr 2345

SELECT substring(col, 1,
len(str) - CASE WHEN str LIKE '%' + char(13)
THEN 1
ELSE 0
END)

1) I've taken you by the word that the character at the end is precisely
CR. You may find that it is LineFeed (char(10)) or CR+LF.

2) I did not take you by the word on the data type, but assumed that
when you said "text" you in fact mean a varchar column. If the data
type actually is text, I don't know for sure if the above will
work.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Remove the last carriage return and line feed from sql text field
    ... strip off the last carriage return and line feed from a text field. ... This causes havoc when we sync between our ERP system and CRM ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ... I did mean that the data type was actually text which is what is ...
    (comp.databases.ms-sqlserver)
  • Re: Remove Carriage Return
    ... *mike hodgson* |/ database administrator/ | mallesons stephen jaques ... >>Database change management for SQL Server ... >>>I attempt to replace the Carriage Return with Space. ...
    (microsoft.public.sqlserver.server)
  • Re: Select Formatted Text From Table
    ... text (with carriage returns, tabs, and spaces). ... If you are using SQL Server Management Studio, then you can see the data ... "Query" menu. ...
    (microsoft.public.sqlserver.mseq)
  • Re: SQL Server 2000 versus Pervasive.SQL
    ... It all comes down to costs vs benefits. ... the ERP system using it) is entirely adequate for the needs of your ... Apparently you perceive SQL Server to be superior in your ... Last side note - The underlying technology does not guarantee a "good" ...
    (microsoft.public.sqlserver.server)
  • Remove the last carriage return and line feed from sql text field
    ... strip off the last carriage return and line feed from a text field. ... This causes havoc when we sync between our ERP system and CRM ... 1234 Blah Street ...
    (comp.databases.ms-sqlserver)