The builtin QUOTENAME function (see the BOL entry) can be used to correctly quote and escape database object names. This function simply provides the reverse functionality - it unquotes and un-escapes a quoted name.

The builtin function supports user-definable quote characters but defaults to square brackets ([ and ]) - the function below is hard coded to support square brackets only. If the input does not appear to be quoted it is returned unchanged.

This function is suitable for SQL Server 2005 and 2008 as well as SQL Server 2000 (just remove the RETURNS NULL ON NULL INPUT function option).

create function dbo.udf_UnQuoteName(@name nvarchar(258)) returns sysname 
as
/*
** Performs the inverse of the built-in QUOTENAME function.
*/
begin

   declare @r sysname

   if (left(@name, 1) = N'[' and right(@name, 1) = N']')
   begin
      -- @Name appears to be quoted, so remove first and last character and doubled closing quotes.
      set @r = replace(substring(@name, 2, len(@name) - 2), N']]', N']')
   end
   else
   begin
      -- @Name does not appear to be quoted so just return the input.
      set @r = @name
   end

   return @r

end
go

-- Tests / Examples
print dbo.udf_UnQuoteName('[SomeName]')
go