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

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

CREATE DATABASE PL;

USE PL;

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF (OBJECT_ID('dbo.udf_QuoteName', 'FN') IS NOT NULL)

BEGIN DROP FUNCTION dbo.udf_QuoteName; END

/*=============================================

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)

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

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;

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';

SELECT *

FROM #DBObjects;

The data in #DBObjects

Let’s check QUOTENAME()

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

SELECT

QUOTENAME(SchemaName) AS SchemaName

, QUOTENAME(TableName) AS TableName

FROM #DBObjects;

Test the function

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

SELECT

dbo.udf_QuoteName(SchemaName) AS SchemaName

, dbo.udf_QuoteName(TableName) AS TableName

FROM #DBObjects;

Only the outer square brackets are manipulated

Keep it simple :-)