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.
Best Answer
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:
or
or if you are planning to insert to another table, it will implicitly cast if the target datatype is variable length and compatible:
If you slightly modify your statement above, you should notice that there are no trailing X's any longer: