T-SQL Server 2012 QUOTENAME issue with first instance of string

Question

I'm trying to use QUOTENAME to enclose each part of the string returned in brackets but cannot seem to find the solution to enclose the first instance of the string?

    CREATE TABLE dbo.Products
(
  ProductID INT PRIMARY KEY,
  Name      NVARCHAR(255) NOT NULL UNIQUE
  /* other columns */
);
INSERT dbo.Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin'),
(4, N'blat');

DECLARE @Split varchar (1000)
SET @Split = ''

SELECT @Split = 
    CASE WHEN @Split = ''
    THEN Name
   ELSE @Split + coalesce(',' + QUOTENAME(Name),'')

    END
  FROM dbo.Products

PRINT @Split

Result :

bar,[blat],[foo],[kin]

I need 'bar' to also be enclosed with [].

Hi, I just edited your question and removed code for `OrderDetail`. This seems not to be needed here. Please do a rollback if I took this wrong... — Shnugo, Oct 27 '16 at 09:12

score 3 · Accepted Answer · answered Oct 27 '16 at 09:08

Your approach to concatenate all values of dbo.Products is better to be done like this:

SELECT STUFF(
(
    SELECT ',' + QUOTENAME(Name)
    FROM dbo.Products
    FOR XML PATH('')
),1,1,'');

But to answer your actual question:

You must use QUOTENAME in the first place as well!

SELECT @Split = 
    CASE WHEN @Split = ''
    THEN Name <-- Use QUOTENAME here as well
   ELSE @Split + coalesce(',' + QUOTENAME(Name),'')

score 1 · Answer 2 · answered Oct 27 '16 at 09:10

1

In your case statement you are missing quotename()

WHEN @Split = ''
    THEN QUOTENAME(Name)

I hope this helps.

Best Regards,
Karthik

answered Oct 27 '16 at 09:10

Karthik Sreenivasan

48
5

T-SQL Server 2012 QUOTENAME issue with first instance of string

2 Answers2