Is it possible to use pattern matching in SQL query to reduce the name of columns returned

Question

I am going to write some custom code to reduce the length of the columns returned. It is just that I wanted to know if it is possible to do it when I get the data itself.

For example the query returns: SELECT * FROM sillytable

This_part_always_remains_same_for_all_columns_Date	This_part_always_remains_same_for_all_columns_Value
18/01/2021	34
17/01/2021	45

Is there something I can do in the query that it returns

Manf_Date	Manf_Value
18/01/2021	34
17/01/2021	45

Essentially I want to replace the This_part_always_remains_same_for_all_columns with Manf in the results returned.

Don't do `select *` in the first place, alias every column with a short name. Better, give columns short names to start with (but not too short) — Charlieface, Jan 17 '21 at 23:01
You need to use an explicit `as` to assign the columns new names. — Gordon Linoff, Jan 17 '21 at 23:04
This table has 100+ column names. And that can change in future. :'-( — NotAgain, Jan 17 '21 at 23:07
@NotAgainsaysReinstateMonica You said you were going to write code. So get started. Nothing will automatically do that for you. One workaround is to create a view to do that - and yes that will also require some repetitive coding. You might be able to use dynamic sql to generate the DDL for each view that removes the repeating bits. — SMor, Jan 18 '21 at 00:15
@SMor Yes I am already working on custom code on the client side to make it a bit more manageable. — NotAgain, Jan 18 '21 at 00:32
@Charlieface The table is simply getting created by Kepware server pumping sensor data into the table. I do not have much control over it. — NotAgain, Jan 18 '21 at 00:33

score 1 · Answer 1 · answered Jan 18 '21 at 00:20

1

If you are open to dynamic SQL than you could do this:

DECLARE @sql varchar(max) 
SELECT @sql = 'SELECT ' + 
              string_agg(name + ' as ' + 
              replace(name, 'This_part_always_remains_same_for_all_columns', 'Manf_'), ',') + 
             ' FROM SillyTable'
FROM sys.columns
WHERE object_id = OBJECT_ID('sillytable')

exec(@sql)

This returns:

Manf__Date  Manf__Value
2021-01-18  34
2021-01-17  45

Though I'm not sure why you wouldn't change the column names in the table.

answered Jan 18 '21 at 00:20

Steve Ford

7,433
19
40

The data is getting pumped into table via Kepware. I do not have much control over the column names. – NotAgain Jan 18 '21 at 00:31
Does this answer your question though? on the column name issues, 2 approaches, rename the columns and add a view which gives the names required by kepware, or keep the column names but add a view to shorten the names. You could use a variation of my script to create the views. Of course the other approach is to change how the columns are displayed in your front-end code. – Steve Ford Jan 18 '21 at 09:21
I ended up writing custom code to change the names of columns after the data pull. – NotAgain Jan 18 '21 at 21:57

score 0 · Answer 2 · answered Jan 17 '21 at 23:08

0

In SQL, you can alias the column names in the select statement with as:

select
   This_part_always_remains_same_for_all_columns_Date as [Manf_Date],
   This_part_always_remains_same_for_all_columns_Value as [Manf_Value]
from sillytable

answered Jan 17 '21 at 23:08

Scott Hoffman

370
2
10

This table has 100+ column names. And that can change in future. :'-( – NotAgain Jan 17 '21 at 23:10

score 0 · Answer 3 · answered Jan 17 '21 at 23:22

What you need to do is to get all the columns of this table to a new table, look here for a sample . Now you have a temp table with
This_part_always_remains_same_for_all_columns

and then
_date
_value
_time

so use REPLACE function (or other string manipulation) to achieve your needs.

score 0 · Answer 4 · answered Jan 18 '21 at 00:19

I will preface by saying: you probably shouldn't have 100 column tables, and you very rarely should be using select * on a table anyway.

Be that as it may, here is a neat solution using dynamic SQL. We just concatenate all the columns together, then execute a select:

DECLARE @sql nvarchar(max) =
(
SELECT N'SELECT ' +
    STRING_AGG(CAST(
        c.name + N' AS ' + REPLACE(c.name, 'This_part_always_remains_same_for_all_columns', 'Manf') AS nvarchar(max))
    , ', ') +
    N' FROM sillytable;'
FROM sys.columns c
WHERE object_id = OBJECT_ID('tablename')
);

EXEC(@sql);

Is it possible to use pattern matching in SQL query to reduce the name of columns returned

4 Answers4