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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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; |
The data in #DBObjects
Let’s check QUOTENAME()
|
1 2 3 4 5 |
SELECT QUOTENAME(SchemaName) AS SchemaName , QUOTENAME(TableName) AS TableName FROM #DBObjects; GO |
Test the function
|
1 2 3 4 5 |
SELECT dbo.udf_QuoteName(SchemaName) AS SchemaName , dbo.udf_QuoteName(TableName) AS TableName FROM #DBObjects; GO |
Only the outer square brackets are manipulated
Keep it simple :-)