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

[whitej77777]

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

[Erland Sommarskog]

(wh*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[whitej77777]

On Jun 12, 5:14 pm, Erland Sommarskog <[e-mail]:

([e-mail]) 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, [e-mail]

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.

[Erland Sommarskog]

(wh*********@gmail.com) writes:

On Jun 12, 5:14 pm, Erland Sommarskog <[e-mail]:

> ([e-mail]) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[whitej77777]

On Jun 15, 5:08 pm, Erland Sommarskog <[e-mail]:

([e-mail]) writes:

On Jun 12, 5:14 pm, Erland Sommarskog <[e-mail]:

([e-mail]) 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, [e-mail]

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

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.