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

Question

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)
-: | :---------------
 1 | obj1
 2 | obj2
 3 | obj3
 4 | obj4  

But my question is:

• Is there any hidden built-in function that removes brackets using T-SQL?

dbfiddle here

Answer 1

Is there any hidden built-in function that removes brackets using T-SQL?

No, not using T-SQL.

OBJECT_ID is an intrinsic function. It is implemented directly in the SQL Server executable code, not in T-SQL; and it does not call any T-SQL when invoked.

At runtime, the object id is obtained via the expression service calling sqlmin!I4ObjIdWstr.

The implementation then goes through all the necessary steps to resolve the provided string parameter(s) into the id of an object in the referenced database.

One of the first steps includes dealing with any delimited identifiers in the string via sqlmin!CbParseQuotesW. In a narrow sense, that is the code function you are referring to, but it is not accessible directly from T-SQL. It contains the following code:

cmp     r9d,22h
je      sqlmin!CbParseQuotesW+0x185
cmp     r9d,2Eh
je      sqlmin!CbParseQuotesW+0x139
cmp     r9d,5Bh
je      sqlmin!CbParseQuotesW+0xfe
cmp     r9d,5Dh
je      sqlmin!CbParseQuotesW+0xda

...which are tests to handle the characters:

• hex 22 = dec 34 = "
• hex 2E = dec 46 = .
• hex 5B = dec 91 = [
• hex 5D = dec 93 = ]

The rest of the process to resolve the parameters to an id involves:

• Starting an automatic read-only transaction
• Checking contained database requirements
• Iterating through possible matches for the name parameter (using the correct collation)
  • In the supplied database name (or current context database)
  • In the supplied schema name (or sys, or the user's default schema etc.)
• Taking the required metadata locks
• Consulting the metadata cache for a match
• Fetching metadata into cache if necessary
• Checking permissions (to access the object id)
• Returning the id of the first matched object (if any)

---

On a side note, the code in the question:

IF OBJECT_ID('TEST') IS NOT NULL

...does not look only for tables. To that would require using the second function parameter. In addition, it only looks for any schema-scoped object named TEST - so a view named BananaSchema.TEST would match, for example. A better expression would be:

IF OBJECT_ID(N'dbo.TEST', N'U') IS NOT NULL

Related Q & A:

• What allows SQL Server to trade an object name for a string passed to a system procedure

Answer 2

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.

Answer 3

SQL Server obviously has something internal that strips out [square brackets] (or other identifiers, like "double quotes").

When you create a table like [dbo].[foo], you're right, only foo gets stored in sys.tables and sys.objects, and there is no complaining that the schema [dbo] (with the square brackets) wasn't found.

But this happens inside the code for CREATE TABLE. They may be using PARSENAME(), as David pointed out. Hooking up the debugger could indicate for sure, but does it matter?

You can look elsewhere to see what they are doing, and sys.sp_rename in fact does yield that PARSENAME() is used:

select @UnqualOldName = parsename(@objname, 1),
        @QualName1 = parsename(@objname, 2),
        @QualName2 = parsename(@objname, 3),
        @QualName3 = parsename(@objname, 4)

But again, I'm not sure I understand why you want to only sometimes remove the square brackets.

For me, personally, a large enough percentage of my code is written for a broader audience, who will use the code in environments where I have no knowledge or control over whether they are using unsafe identifiers. So I always have and always will write (and prefer) code that uses QUOTENAME() to generate scripts that include any kind of identifier.

I would much rather have the square brackets there all the time, than take them away and get bitten the one time they were needed.

Answer 4

Wrote this function in the past:

create or alter function dbo.UNQUOTENAME(@fragment varchar(1000), @unquote varchar(16) = '()[]{}""')
returns varchar(1000)
as
    begin
        declare @position int = 1;
        declare @numberOfOptions int = len(@unquote) / 2;
        declare @tryCount int = 0;

        while (1=1)

            begin

                if left(@fragment,1) = substring(@unquote,@position,1)

                    if right(@fragment,1) = substring(@unquote,@position+1,1)

                        begin

                            set @fragment = substring(@fragment,2,len(@fragment)-2);
                            set @tryCount = 0;
                        end;

                    else

                        break;
                else

                    begin

                        set @position += 2;
                        set @tryCount += 1;
                    end;

                if @position > len(@unquote) 

                    begin

                        if @tryCount >= @numberOfOptions
                            break;
                        else
                            set @position = 1;
                    end;
            end;

        return(@fragment);
    end
GO

Answer 5

When square brackets are needed -- it is because your identifier is already a reserved keyword. Using them arbitrarily is not only not necessary, it leads to much confusion as you can see.

In my opinion, the best way is to avoid using reserved identifiers in the first place.