T-SQL: UDF Scalar Function to quote the DB Object Name

While working on ETL, I build a list of tables and their corresponding columns that need to be created on the destination server. In this case the square brackets around SchemaName and TableName are mandatory. I started adding the square brackets with the built-in function QUOTENAME(), but soon I got a “dirty” data and created a user-defined scalar function that adds square brackets around SchemaName and TableName.

Create database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

Create function

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF (OBJECT_ID('dbo.udf_QuoteName', 'FN') IS NOT NULL)
	BEGIN DROP FUNCTION dbo.udf_QuoteName; END
GO

/*=============================================
Author:				Peter Lalovsky
Create date:	2017-10-27
Description:	Extend the functionality of QUOTENAME() by removing additional square brackets
=============================================*/

CREATE FUNCTION dbo.udf_QuoteName (@String NVARCHAR(255))
RETURNS NVARCHAR(255)
AS
BEGIN
	DECLARE @ReturnValue NVARCHAR(255)

	DECLARE
		@Position INT = 1
		, @LenString INT;

	SELECT @LenString = LEN(@String);

	-- Left Square Brackets
	WHILE (@Position <= @LenString)
	BEGIN
		IF LEFT(@String, 1) = '['
			BEGIN SET @String = RIGHT(@String, LEN(@String) - 1); END
		ELSE
			BEGIN BREAK; END
	
		SELECT @Position += 1;
	END

	-- Right Square Brackets
	SELECT @Position = 1;

	WHILE (@Position <= @LenString)
	BEGIN
		IF RIGHT(@String, 1) = ']'
			BEGIN SET @String = LEFT(@String, LEN(@String) - 1); END
		ELSE
			BEGIN BREAK; END
	
		SELECT @Position += 1;
	END

	SELECT @ReturnValue = CONCAT('[', @String, ']');

	--RETURN QUOTENAME(REPLACE(REPLACE(@String, '[', ''), ']', ''))
	RETURN @ReturnValue;
END
GO

The highlighted line (row 55) shows another approach that bulk replace the square brackets and adds QUOTENAME()

To test the function, we need a table with sample data

IF (OBJECT_ID('tempdb.dbo.#DBObjects', 'U') IS NOT NULL)
	BEGIN DROP TABLE #DBObjects; END

SELECT '[[Schema Name 1]]]' AS SchemaName, 'Table Name 1]' AS TableName
INTO #DBObjects
UNION ALL SELECT 'Schema Name 2]', '[Table Name 2'
UNION ALL SELECT 'Schema Name 3', '[Table Name 3]'
UNION ALL SELECT 'Schema Name 3', 'Table Name 4'
UNION ALL SELECT 'Schema Name 3', 'Table Name 4'
UNION ALL SELECT 'Schema [ Name 3', 'Table] Name 4';

SELECT *
FROM  #DBObjects;

Let’s check QUOTENAME()

SELECT
	QUOTENAME(SchemaName) AS SchemaName
	, QUOTENAME(TableName) AS TableName
FROM #DBObjects;
GO

Test the function

SELECT
	dbo.udf_QuoteName(SchemaName) AS SchemaName
	, dbo.udf_QuoteName(TableName) AS TableName
FROM #DBObjects;
GO

Keep it simple :-)