CODEWITHSUNDEEP
sqlsql-servert-sql
Feanir
Feanir

Reputation: 11

Rename column with quotation mark on start of its name

I have table with unknown column's names from import and I'm trying to rename its. For example:

DECLARE @Col1 nvarchar(128);

SELECT @Col1 = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = mytable and ordinal_position = 1;

DECLARE @ColName nvarchar(255);
SET @ColName = '[mytable.' + @Col1 + ']';
sp_RENAME @ColName, '[MyColumn]', 'COLUMN';

It doesn't work when @Col1 starts from quotation mark like "RudeColumName and returns: Incorrect syntax near 'sp_RENAME'. Also rename by using ALTER TABLE doesn't work and returns syntax error.

How could I rename this column?

Upvotes: 1

Views: 98

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

QUOTENAME() is the right function to use. This is how you would use it:

DECLARE @Col1 nvarchar(128);

SELECT @Col1 = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = mytable and ordinal_position = 1;

DECLARE @ColName nvarchar(255);
SET @ColName = QUOTENAME('mytable') + '.' + QUOTENAME(@Col1);

sp_RENAME @ColName, '[MyColumn]', 'COLUMN';

Note: it is used separately for the table name and the column name.

Of course, you might also need QUOTENAME() around the new name as well. On the other hand, simply don't use new column names that require quoting.

Upvotes: 1

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

How about to use QUOTENAME instead of brackets []?

DECLARE @Col1 nvarchar(128);

SELECT @Col1 = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = mytable and ordinal_position = 1;

DECLARE @ColName nvarchar(255);
SET @ColName = QUOTENAME('mytable.' + @Col1);
sp_RENAME @ColName, '[MyColumn]', 'COLUMN';

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Try this

DECLARE @Col1 nvarchar(128);

SELECT @Col1 = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = mytable and ordinal_position = 1;

DECLARE @ColName nvarchar(255);
SET @ColName = '[mytable.''' + @Col1 + ']';
sp_RENAME @ColName, '[MyColumn]', 'COLUMN';

Upvotes: 0

Related Questions