RTRIM not working on sysfiles column

Question

RTRIM not working on sysfiles column

Stu_H

SSC Enthusiast

Points: 106
More actions
September 13, 2007 at 8:05 pm

#69966

I am trying to get a simple RTRIM function working. I am running the following query and outputting the results to text (I need the results in text outputted to QA)
select
RTRIM(filename), [name] from sysfiles
The filename column is an nvarchar(520) in SQL 2005. In the results set the column is returned with trailing blanks as if the RTRIM function is ignored.
I tried the following and this also does not work:
select
SUBSTRING(filename, 1, LEN(filename)), name from sysfiles
however if I use the LEN function to calculate the length, e.g. 40 characters and then run
select SUBSTRING(filename, 1, 40), name from sysfiles
I then get the desired result. But I need this to be dynamic. I won't always know the length.
How can I trim the trailing blanks on the filename column? Why doesn't RTRIM work?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Oh... it works... it's just the text mode display has a mind of it's own when it comes to calculated column widths... so you need to do something like this...

DECLARE @MaxWidth INT
 SELECT @MaxWidth = MAX(LEN(FileName)) FROM Master.dbo.SysFiles
DECLARE @SQL VARCHAR(8000)
    SET @SQL = 'SELECT LEFT(FileName,'+STR(@MaxWidth)+') FROM Master.dbo.SysFiles'
   EXEC (@SQL)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

No need for RTRIM at all with LEN function.

DECLARE

@s-2 NVARCHAR(520)

SET

@s-2 = N'Peso' + SPACE(5)

SELECT

@s-2, LEN(@s-2), DATALENGTH(@s-2)

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Peter... try SUBSTRING with a variable for length or RTRIM in the text mode on a wide column to see what the OP means... displayed column comes out too wide...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 4

You can use simple left function:

select name=left(name, 30)

from sysfiles

Just make sure that the longest file name is smaller than this number.

It is sometimes inconvenient when SSMS allocates 255 character places for values which are much smaller than that. Alternatively you can use grid mode by pressing Ctrl D.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Yes... that will work but it's not "dynamic" like the Op asked... if the length is a variable, it will not display as expected in the Text Pane.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

How about 2 * LEN() because it NCHAR?

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Heh... did you try it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stu_H SSC Enthusiast Points: 106 More actions · Answer 8

Jeff - thank you for your help, your solution works a treat and is dynamic as required. It is slightly annoying that these extra lines of code and dynamic SQL is required. Any ideas why RTRIM appears not to work when outputting to text?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

RTRIM does work. You are just mistaken in assuming that SQL Server will adjust the column width to match the maximum size of the trimmed column. SQL Server just makes the output column the size of the source column.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 10

Keep it simple:

    declare @Filename   nvarchar( 260 ),
            @Name       nvarchar( 128 );

    select  @Filename   = rtrim( filename ),
            @Name       = rtrim( name )
    from    sysfiles;

    select  @Filename as Filename, @Name as Name;

Simple, dynamic, does everything you want.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

The script on the link below may be useful for what you are trying to do.

Get Server Database File Information

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 12

It just occurred to me that you may want more than one row. In that case, select into a temp table or table variable.

    declare @Sysfiles   table (
        [filename] nvarchar( 260 ),
        [name]     nvarchar( 128 )
    )
    insert into @Sysfiles
    select  rtrim( filename ),
            rtrim( name )
    from    sysfiles;

    select  * from @Sysfiles;

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

Michaeal said it right...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

How does that produce the dynamic sizing the OP asked for? That code works great on the input but does nothing for formatting the output in the text window.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)