[RESOLVED] Trim a carriage return from end of string

[FishGuy]

I need to alter a select statement so that a returned field does not have an unwanted carriage return at the end.

I think the code below would remove all the carriage returns but I only want to remove one if it appears at the very end of the string.

SELECT REPLACE([parameters],Char(10),'') as [parameters]
FROM QUERIES

[si_the_geek]

Which database system(s) are you using?

[FishGuy]

Sorry it is SQL Server 2010.

[si_the_geek]

If it is just Char(10) you are interested in, something like this should do it:

SELECT CASE WHEN Right([parameters],1) = Char(10) 
            THEN Left([parameters], Length([parameters])-1)
            ELSE [parameters]
            END as [parameters]
FROM QUERIES

If you want to allow a mixture of Char(10) and Char(13) it gets a bit more awkward, and it would probably be better to create a function.

[GaryMazzone]

I did not know there was a SQL Server 2010. Where did you find that. I thought the last relase was SQL Server 2008R2 and the SQL Server "Denali" CTP1 was the latest preview.

[Pradeep1210]

(not tested code)

CREATE FUNCTION RTrimChar(@Input VARCHAR(MAX), @TrimChar CHAR(1))
RETURNS VARCHAR(MAX)
AS
BEGIN
    WHILE RIGHT(@Input, 1) = @TrimChar
    SET @Input = LEFT(@Input, LEN(@Input)-1)
    RETURN @Input
END

[FishGuy]

If it is just Char(10) you are interested in, something like this should do it:

SELECT CASE WHEN Right([parameters],1) = Char(10) 
            THEN Left([parameters], Length([parameters])-1)
            ELSE [parameters]
            END as [parameters]
FROM QUERIES

If you want to allow a mixture of Char(10) and Char(13) it gets a bit more awkward, and it would probably be better to create a function.

I In actual fact I do want to remove Char13 but I need to remove all Char13 from the string but only remove a Char10 if it is at the end of the string.

REPLACE(CASE WHEN Right([parameters],1) = Char(10)
THEN Left([parameters], Len([parameters])-1)
ELSE [parameters]
END,Char(13),'')
as [parameters]

Currently tried this but it still does not seem to remove the extra carriage return at the end of my string.

If I copy and paste the output to word here is what it returns (hope you can figure out what I I mean by the improvisation of symbols at the end of each row.).

Start Date : 07/12/2010<&#172;
End Date : 08/12/2010<&#172;
Include IT : Y<&#172;
Report Names : Select All P|
P|

[si_the_geek]

Try adding Pradeep1210's function, then using this:

SELECT RTrimChar (REPLACE([parameters],Char(13),''), Char(10))
...

[FishGuy]

I just tried that, is it possible that there are two CRs at the end of the string, how would the function handle that?

Alternatively, is there another char that could be causing this i.e. not char10 or 13?

[Pradeep1210]

Just like RTrim function removes all trailing spaces, this function removes all trailing custom character you specify. Doesn't matter how many they are.

[si_the_geek]

What it wont handle however is any other characters (such as spaces) between those characters, so it may be a good idea to add this:

    SET @Input = RTrim(LEFT(@Input, LEN(@Input)-1))

[FishGuy]

Still not working, is there any way to get the actual char output from the data to see what is going there.

[FishGuy]

Thanks guys, that now works.