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

On Jun 12, 5:14 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
(whitej77...@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, esq...@xxxxxxxxxxxxx

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -

I did mean that the data type was actually text which is what is
probably causing the most problem.

.

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 ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • 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)
  • 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)
  • Re: PDM to MRP
    ... For communication with any ERP system we always ask the following ... Does it use a relational database such as SQL Server or Oracle? ...
    (comp.cad.solidworks)
  • do I need a DBA for SQL Server?
    ... We currently run an Oracle 8i db for Oracle Applications 10.7. ... Server and SQL Server 2005 with a new ERP system. ... We have plenty of Windows ...
    (comp.databases.ms-sqlserver)
Loading