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

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
Suite 2345

Corrected Text field:

1234 Blah Street
Suitr 2345

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

(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
> Suite 2345
>
> Corrected Text field:
>
> 1234 Blah Street
> 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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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

On Jun 12, 5:14 pm, Erland Sommarskog 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
> > Suite 2345
>
> > Corrected Text field:
>
> > 1234 Blah Street
> > 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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.

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

(whitej77777@gmail.com) writes:
> On Jun 12, 5:14 pm, Erland Sommarskog wrote:
>> (whitej77...@gmail.com) writes:
>> SELECT substring(col, 1,
>> len(str) - CASE WHEN str LIKE '%' + char(13)
>> THEN 1
>> ELSE 0
>> END)
>
> I did mean that the data type was actually text which is what is
> probably causing the most problem.

So did my SELECT work for you?

I can spot one change that is needed: use datalength() rather than
len(), as len() does not work past the 8000-character limit.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

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

On Jun 15, 5:08 pm, Erland Sommarskog wrote:
> (whitej77...@gmail.com) writes:
> > On Jun 12, 5:14 pm, Erland Sommarskog wrote:
> >> (whitej77...@gmail.com) writes:
> >> SELECT substring(col, 1,
> >> len(str) - CASE WHEN str LIKE '%' + char(13)
> >> THEN 1
> >> ELSE 0
> >> END)
>
> > I did mean that the data type was actually text which is what is
> > probably causing the most problem.
>
> So did my SELECT work for you?
>
> I can spot one change that is needed: use datalength() rather than
> len(), as len() does not work past the 8000-character limit.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Well I modified it slightly to get it to work but thank you for the
help. Here is what I used:

substring(fmstreet, 1, datalength(fmstreet) - CASE WHEN fmstreet LIKE
'%' + char(13) + char(10) THEN 2 ELSE 0 END)

Thanks again.