Is there a way to remove '_' from column name while selecting * in sql statement?

Question

My table has all the column names (There are more than 80 columns, I can't change the column names now) in the format of '_'. Like First_Name, Last_Name,...

So i want to use select * from table instead of using AS.

I want to select them by removing '_' in one statement. Anyway i can do it? something like Replace(coulmnName, '_','') in select statement ?

Thanks

You can better create a view and then use select * from view — Kiran Hegde, Jul 21 '14 at 14:07
You can't in SQL. How are you consuming he query? Could you change the column names on the client side? — D Stanley, Jul 21 '14 at 14:15

score 5 · Answer 1 · edited May 23 '17 at 12:21

5

You can simply rename the column in your query. For example:

SELECT FIRST_NAME [First Name],
       LAST_NAME [Last Name]
  FROM UserTable

You can also use the AS keyword but this is optional. Also note that if you don't want to do this on every query you can use this process to create a view with renamed columns. Then you can use SELECT * the way you want to (although this is considered a bad idea for many reasons).

Best of luck!

Alternative - Map In The Client Code:

One other alternative is to do the mapping in the client code. This solution is going to depend greatly on your ORM. Most ORM's (such as LINQ or EF) will allow you to remap. If nothing else you could use AutoMapper or similar to rename the columns on the client using convention based naming.

edited May 23 '17 at 12:21

Community

1
1

answered Jul 21 '14 at 14:07

drew_w

10,320
4
28
49

I use `AS` in Oracle 11. What version do you use? – Michael McGriff Jul 21 '14 at 14:08
But i don't want to explicitly declare 80+ column name in the query. Any other way? – user1882705 Jul 21 '14 at 14:08
You could generate your SQL-Script - either via select column_name from all_tab_columns where table_name = ...; And then use a text-editor and some search and replace - or you could write a pl/sql routine for this... – Falco Jul 21 '14 at 14:14
1

@MichaelMcGriff I have had tons of issues with `AS` on 9i and 10g. That said, I removed the comment since officially this should work on oracle. – drew_w Jul 21 '14 at 14:14
@drew_w I've never had trouble on 11. +1 for mentioning the real problem is using `SELECT *`. – Michael McGriff Jul 21 '14 at 14:17
The OP appears to be using SQL Server. Why all the Oracle references? Am I missing something? – Dave Mason Jul 21 '14 at 14:27
@DMason Just referencing a side comment I made about Oracle in a previous version of the reply. – drew_w Jul 21 '14 at 14:28

score 1 · Answer 2 · answered Jul 21 '14 at 14:22

You can't do this in a single statement unless you're using dynamic SQL. If you're just trying to generate code, you can run a query against Information_Schema and get the info you want ...

DECLARE @MaxColumns INT
DECLARE @TableName VARCHAR(20)
SET @TableName = 'Course'
SELECT @MaxColumns = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName 


SELECT Col 
FROM 
  (
    SELECT 0 Num, 'SELECT' Col
    UNION
    SELECT ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY ORDINAL_POSITION) Num, ' [' + COLUMN_NAME + '] AS [' + REPLACE(COLUMN_NAME, '_', '') + ']' + CASE WHEN ORDINAL_POSITION = @MaxColumns THEN '' ELSE ',' END
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = @TableName
    UNION 
    SELECT @MaxColumns + 1 Num, 'FROM ' + @TableName 
  ) s 
ORDER BY num

If you have multiple schemas, of course, you'll need to add a filter for the right one ... — AHiggins, Jul 21 '14 at 14:24

score 0 · Answer 3 · answered Jul 24 '14 at 11:26

The question intrigued me and I did find one way. It makes it happen but if you just wanted to give a lot of aliases one time in one query I wouldn't recommend it though.

First I made a stored procedure that extracts all the column names and gives them an alias without '_'.

USE [DataBase]
GO
IF OBJECT_ID('usp_AlterColumnDisplayName', 'P') IS NOT NULL
    DROP PROCEDURE usp_AlterColumnDisplayName
GO

CREATE PROCEDURE usp_AlterColumnDisplayName 
@TableName VARCHAR(50)
,
@ret nvarchar(MAX) OUTPUT
AS

Select @ret = @ret + [Column name]
From
(
    SELECT ([name] + ' AS ' + '[' + REPLACE([name], '_', ' ') + '], ') [Column name]
    FROM syscolumns
    WHERE id =
        (Select id
        From sysobjects
        Where type = 'U'
        And [name] = @TableName
    )
) T
GO

Then extract that string and throw it into another string with a query-structure.

Execute that and you are done.

DECLARE @out NVARCHAR(MAX), @DesiredTable VARCHAR(50), @Query NVARCHAR(MAX)
SET @out = ''
SET @DesiredTable = 'YourTable'
EXEC usp_AlterColumnDisplayName 
    @TableName = @DesiredTable,
    @ret = @out OUTPUT
SET @out = LEFT(@out, LEN(@out)-1) --Removing trailing ', '

SET @Query = 'Select ' + @out + ' From ' + @DesiredTable + ' WHERE whatever'

EXEC sp_executesql @Query

If you just wanted to give a lot of aliases at once without sitting and typing it out for 80+ columns I would rather suggest doing that with one simple SELECT statement, like the one in the sp, or in Excel and then copy paste into your code.

Is there a way to remove '_' from column name while selecting * in sql statement?

3 Answers3