tsql cmd to strip returns

Posted on 2002-03-12
Medium Priority
Last Modified: 2006-11-17
I need the a tsql command that will strip of instances of a _trailing_ carragereturn/linefeed from a given field in a table.

Question by:juststeve
  • 2
LVL 70

Accepted Solution

Scott Pletcher earned 300 total points
ID: 6858061
Try one of these:

To UPDATE the column:
UPDATE tablename
SET column = LEFT(column,LEN(column) - 2)
WHERE RIGHT(column,2) = CHAR(13)+CHAR(10)

To SELECT the column:
SELECT CASE RIGHT(column,2) = CHAR(13)+CHAR(10) THEN LEFT(column,LEN(column) - 2) ELSE column END
FROM tablename

Author Comment

ID: 6858249
thankx Scott....it appears to do what I want. The bit about the 'To SELECT the column:'... that's just so I can see what I'm going to update before I do so?
LVL 70

Expert Comment

by:Scott Pletcher
ID: 6858320
Exactly; most people prefer to see what an update's going to do before they actually issue it.  Normally I would list the old and the new values but I didn't think you'd be able to see any difference in this case :-).

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question