|
|
Posted by whitej77777 on 06/15/07 12:29
On Jun 12, 5:14 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (whitej77...@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, esq...@sommarskog.se
>
> 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.
Navigation:
[Reply to this message]
|