|
|
Posted by Erland Sommarskog on 06/12/07 21:14
(whitej77777@gmail.com) 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@sommarskog.se
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
Navigation:
[Reply to this message]
|