Is there any (hidden) built-in function on MS-SQL to unquote object names?

Sometimes I store object names (identifiers) in some of our databases, for example in some parameter tables. Because I select records from these tables using the '=' or 'LIKE' comparison operators, I must take care to store these names always with or without brackets.

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = '[TABLE_NAME]';

or

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = 'TABLE_NAME';

However MS-SQL has some functions where you can use object names with or without brackets, for example the OBJECT_ID() function. I've set up a minimal example on dbfiddle.uk.

CREATE TABLE TEST
(
    ID     INT IDENTITY(1,1) PRIMARY KEY,
    OBJECT sysname NOT NULL
);
GO

INSERT INTO TEST VALUES ('[obj1]'),('obj2'),('obj3'),('[obj4]');
GO

Now I can use OBJECT_ID() to check if the table TEST exists in this way:

IF OBJECT_ID('TEST') IS NOT NULL
BEGIN
    SELECT 'TEST EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID    |
| :----------- |
| TEST EXISTS. |

IF OBJECT_ID('[TEST]') IS NOT NULL
BEGIN
    SELECT '[TEST] EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID      |
| :------------- |
| [TEST] EXISTS. |

It doesn't matter if I pass the identifier TEST with or without brackets, parser is smart enough to remove the brackets.

Well, I can simulate this by adding a scalar function that remove brackets from one string:

CREATE FUNCTION UNQUOTENAME(@TXT NVARCHAR(MAX)) 
RETURNS NVARCHAR(MAX)
AS
    BEGIN
        RETURN IIF(LEFT(@TXT, 1) = N'[' AND RIGHT(@TXT, 1) = N']', 
                   SUBSTRING(@TXT, 2, LEN(@TXT) -  2), 
                   @TXT);
    END;
GO

And then use it in this way:

```
SELECT dbo.UNQUOTENAME (N'[FIELD]') NAME1, N'FIELD' NAME2;
GO

NAME1 | NAME2
:---- | :----
FIELD | FIELD

SELECT ID, OBJECT
FROM TEST
WHERE OBJECT LIKE 'obj%';
GO

ID | OBJECT
-: | :-----
2 | obj2
3 | obj3

SELECT ID, dbo.UNQUOTENAME(OBJECT)
FROM TEST
WHERE dbo.UNQUOTENAME(OBJECT) LIKE 'obj%';
GO

ID | (No column name)
-: | :---------------

Sometimes I store objects names in some of our databases

I must take care to store this names always with or without brackets.

An "object name" is technically called an identifier. In some contexts an identifier will be appear TSQL code surrounded by [ and ] or " and ". These characters are not part of the identifier, and you should never store them.

Instead store the identifier as a nvarchar(128) (or sysname), and add the delimiters at runtime using the QUOTENAME function.

The inverse of QUOTENAME is PARSENAME, which has the additional ability to navigate multi-part names.

Note that QUOTENAME has an optional second parameter, and if you specify a single quote character for that parameter QUOTENAME does not create a valid delimited identifier expression. It emits a varchar literal expression.

StackExchange Database Administrators Q#195923, answer score: 20