Carriage returns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Carriage returns

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-01-22 : 14:00:48

Would it be possible to identify the number of carriage returns at the end of a string and delete them?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-22 : 14:34:54

The RTRIM() function should help -- I believe it removes CR's as well as other whitespace characters.

- Jeff

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-01-22 : 14:51:01

Thats Great

But I first need to know how many carriage returns are at the end of the string.
Some of my strings have more than one carriage return due to user input.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-22 : 15:18:23

I think Rtrim() only removes trailing spaces. The following should trim off any trailing carriage returns:

SELECT Reverse(Substring(Reverse(string), patindex('%[^' + char(10)+char(13)+']%', reverse(string)), 8000))

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-22 : 15:24:19

Here is one way to achieve your objective. (for a single instance of a string)


declare	@str varchar(50)
	,@i int
	,@char char(1)

Select	@i = 1
	,@str = '123

'

select len(@str), 'from' + @str + 'to'

--print individual ascii characters to discover what characters have been entered
while @i < len(@str)
begin
	print ascii(subString(@str, @i, 1))
	set @i = @i+1
End
--in the example above, I entered '123' + <enter> key twice.  The 2 <enter> keys turned into char(13) + char(10) + char(13)


--keep deleting the end characters while they are carriage return characters
Select @char = right(@str, 1)
while @char IN (char(10), char(13))
Begin
	set @str = left(@str, len(@str)-1)
	set @char = right(@str, 1)
End

select	len(@str), 'from' + @str + 'to'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources