Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm dealing with a server running SQL Server 2000 SP1, and the logical filenames for one of the databases appear to contain trailing spaces. That is, this query:

select replace(name, ' ', 'X') from sysfiles

Returns the expected names plus a long string of Xs.

How can I deal with this? I've tried running ALTER DATABASE... MODIFY FILE using the name (with and without spaces) and get an error message telling me the file does not exist.

share|improve this question

1 Answer 1

This isn't due to the fact that the names actually have spaces, it's due to the fact that Sql Server stores this data in a field of the type nchar(128), which is a fixed-width character data type (i.e. the data stored will always be 128 characters long, padded with spaces if necessary). So, when you query for that value, you'll always get a type that is 128 characters in length. If you want to trim the trailing space padding of the datatype, simply cast it to a variable-length datatype, or explicitly trim it, like this:

select cast(name as nvarchar(128)) as name from sysfiles

or

select rtrim(name) as name from sysfiles

or if you are planning to insert to another table, it will implicitly cast if the target datatype is variable length and compatible:

create table #files (name nvarchar(128));
insert #files (name) select name from sysfiles;
select name from #files;

If you slightly modify your statement above, you should notice that there are no trailing X's any longer:

select replace(cast(name as nvarchar(128)), ' ', 'X') from sysfiles
share|improve this answer
    
I don't think that's correct, the data type of the 'name' column is 'sysname', which according to Microsoft is 'a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it is not nullable'. –  Luke Girvin Dec 7 '09 at 11:41
    
That's for sql 2005+ - in Sql 2000 (which you are using), it's an nchar type –  chadhoc Dec 8 '09 at 19:05
    
See here for the Sql 2000 documentation on the system level table: msdn.microsoft.com/en-us/library/aa260411(SQL.80).aspx –  chadhoc Dec 8 '09 at 19:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.