CODEWITHSUNDEEP
sqlsql-server
nr-91
nr-91

Reputation: 333

SQL Server - Column Names with Line Breaks

Similar to Column names with line breaks but with SQL Server rather than PostgreSQL.

I have to read a column from a table that has a column with a line break. ie: 

SELECT
[Column
Name]
FROM [dbo].[TableName]

I'm unable to change this schema.

The tool which I'm using to query SQL Server also only allows a single line for a query, so I'm unable to do the exact query above.

How can I write this SELECT query in one line?

Upvotes: 2

Views: 4946

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

Untested but you could try this. I am putting commands on separate lines for readability, but it should work the same when combined all on one line.

Also you might need to confirm exactly which characters are used to cause the line-feed in your column name (and verify there are no other whitespace characters in it):

DECLARE @sql varchar(max);
SET @sql = 'SELECT [Column'+char(10)+char(13)+'Name] FROM [dbo].[TableName]';
EXEC (@sql);

Upvotes: 1

Anssssss
Anssssss

Reputation: 3262

Those are some not-fun requirements you have. Here's a way to do it: do a "select * from dbo.TableName", then get the column value from the results based on ordinal position. How do you get that column's ordinal position without hitting the same problem? I suppose you could do a query like so

select ORDINAL_POSITION from INFORMATION_SCHEMA.columns where table_schema = 'dbo' table_name = 'Table' and column_name = 'Column'+CHAR(13)+CHAR(10)+'Name'

So from the result of that you can then do your "select *" and grab the column value at that position.

Related question whose answers point out that this isn't a great thing to do - Is it possible to select sql server data using column ordinal position

Upvotes: 0

Related Questions