SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEST
(
    ID     INT IDENTITY(1,1) PRIMARY KEY,
    OBJECT sysname NOT NULL
);
INSERT INTO TEST VALUES ('[obj1]'),('obj2'),('obj3'),('[obj4]');
4 rows affected

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


OBJECT_ID


TEST EXISTS.


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


OBJECT_ID


[TEST] EXISTS.


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;
SELECT dbo.UNQUOTENAME (N'[FIELD]') NAME1, N'FIELD' NAME2;


NAME1
NAME2


FIELD
FIELD


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


ID
OBJECT


2
obj2

3
obj3


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


ID
(No column name)


1
obj1

2
obj2

3
obj3

4
obj4