CODEWITHSUNDEEP
sqlsql-serveralias
BilalAhmed
BilalAhmed

Reputation: 191

Set alias for column values

Is it possible to Set Alias for Column Values as we are set for column header in sql server.

Or if there is any other way to convert my column values to readable format for clients.

I have the following System generated values:

BILL_DETAILS
BILLING_MENU
ComplaintNumberInput
CUSTOMER_ACCOUNT_NUMBER_INPUT
DEFAULTER
FAULTS_SHUTDOWN_MENU
KUNDA_CONNECTION
LOAD_SHEDDING_MENU
LOAD_SHEDDING_SCHEDULED
loadSheddingScheduleReplayer
loadSheddingStatus
loadSheddingStatusReplayer
MENU_CONTEXT_EVAL
POWER_COMPLAINTS_MENU
repaetComplaintStatus

Is it possible to change them in the following:

BILL DETAILS
BILLING MENU
COMPLAINT NUMBER INPUT
CUSTOMER ACCOUNT NUMBER INPUT
DEFAULTER
FAULTS SHUTDOWN MENU
KUNDA CONNECTION
LOAD SHEDDING MENU
LOAD SHEDDING SCHEDULED
LOAD SHEDDING SCHEDULE REPLAYER
LOAD SHEDDING STATUS
LOAD SHEDDING STATUS REPLAYER
MENU CONTEXT EVAL
POWER COMPLAINTS MENU
REPEAT COMPLAINT STATUS

Upvotes: 0

Views: 2250

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82524

In sql, an Alias is a different name for a database object. Values does not fall under this category so it's impossible to alias them. You can, however, format the output of your query, though formatting is usually best to do in the presentation layer and not in the data layer.

Having said that, there is a t-sql solution for your question:

SELECT REPLACE(ColumnName, '_', ' ') As ColumnName
FROM TableName

This will convert all underlines to spaces.

To handle the other format you can thank Jeff Moden for solving that problem as well (see this link).

SELECT COALESCE(STUFF(ColumnName, NULLIF(patindex('%[a-z][A-Z]%', ColumnName COLLATE Latin1_General_BIN), 0) + 1, 0, ' '), Col) AS ColumnName
FROM TableName

So combining the 2 solutions your final sql should be something like this:

SELECT REPLACE(COALESCE(STUFF(ColumnName, NULLIF(patindex('%[a-z][A-Z]%', ColumnName COLLATE Latin1_General_BIN), 0) + 1, 0, ' '), ColumnName), '_', ' ') AS ColumnName
FROM TableName

This way you can handle these 2 formats in pure t-sql without having to change your query whenever a new value is added to the table.

Here is a test case with the values you posted:

DECLARE @t TABLE (Col VARCHAR(40))

INSERT INTO @t VALUES
('BILL_DETAILS'),
('BILLING_MENU'),
('ComplaintNumberInput'),
('CUSTOMER_ACCOUNT_NUMBER_INPUT'),
('DEFAULTER'),
('FAULTS_SHUTDOWN_MENU'),
('KUNDA_CONNECTION'),
('LOAD_SHEDDING_MENU'),
('LOAD_SHEDDING_SCHEDULED'),
('loadSheddingScheduleReplayer'),
('loadSheddingStatus'),
('loadSheddingStatusReplayer'),
('MENU_CONTEXT_EVAL'),
('POWER_COMPLAINTS_MENU'),
('repaetComplaintStatus')

SELECT Col
       ,UPPER(REPLACE(COALESCE(STUFF(col, NULLIF(patindex('%[a-z][A-Z]%', Col COLLATE Latin1_General_BIN), 0) + 1, 0, ' '), Col), '_', ' ')) AS NewCol
FROM @t

Results:

Col                                      NewCol

BILL_DETAILS                             BILL DETAILS
BILLING_MENU                             BILLING MENU
ComplaintNumberInput                     COMPLAINT NUMBERINPUT
CUSTOMER_ACCOUNT_NUMBER_INPUT            CUSTOMER ACCOUNT NUMBER INPUT
DEFAULTER                                DEFAULTER
FAULTS_SHUTDOWN_MENU                     FAULTS SHUTDOWN MENU
KUNDA_CONNECTION                         KUNDA CONNECTION
LOAD_SHEDDING_MENU                       LOAD SHEDDING MENU
LOAD_SHEDDING_SCHEDULED                  LOAD SHEDDING SCHEDULED
loadSheddingScheduleReplayer             LOAD SHEDDINGSCHEDULEREPLAYER
loadSheddingStatus                       LOAD SHEDDINGSTATUS
loadSheddingStatusReplayer               LOAD SHEDDINGSTATUSREPLAYER
MENU_CONTEXT_EVAL                        MENU CONTEXT EVAL
POWER_COMPLAINTS_MENU                    POWER COMPLAINTS MENU
repaetComplaintStatus                    REPAET COMPLAINTSTATUS

Upvotes: 1

cosmos
cosmos

Reputation: 2303

Use case statements for each value, like:

case old_column_name
     when 'LOAD_SHEDDING_MENU'
     then 'LOAD SHEDDING MENU'
     when 'loadSheddingScheduleReplayer'
     then 'LOAD SHEDDING SCHEDULE REPLAYER'
     when ...........
     then ...........
end as column_name

Upvotes: 0

Related Questions