2

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?

nr-91
  • 333
  • 4
  • 14
  • 2
    That's a tricky one. You say you can't modify the structure of the table, but could you perhaps add a simple view that returns the contents of this table with less-problematic column names, and then query the view? Or is that also not an option? – Joe Farrell Jul 13 '18 at 19:41
  • 1
    Maybe you could do it with dynamic sql, using `char(10)+char(13)` to build the column name portion of the string. Using semi-colons to separate the commands, you should technically be able to do it all on one line. – Tab Alleman Jul 13 '18 at 19:42

2 Answers2

1

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);
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • 1
    Just a note: I seem to notice that SQL Server and SSSM typically just use CHAR(13) ( ) instead of a CR/LF pair. So if this doesn't work, try with just the char(13) and see if THAT works. – pmbAustin Jul 13 '18 at 20:08
0

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

Anssssss
  • 3,087
  • 31
  • 40