SQL - How to use SUBSTRING function to output as Col Name

Question

Is it possible to use the SUBSTRING function in SQL to output the result as Column Name?

Example:

DECLARE @itemname VARCHAR(50) = 'HELLO WORLD'
SELECT SUBSTRING(@itemname, 1, 5) AS OUTPUT

Gives the output as

OUTPUT
-----
HELLO

What I want to achieve here is that the Col name should instead be given out as HELLO

DECLARE @itemname VARCHAR(50) = 'HELLO WORLD'

SELECT col1 AS SUBSTRING(@itemname, 1, 5)
FROM myTable

Which should display

HELLO
------
Record 1
Record 2
Record 3
Record 4
.
.
.

No, you cannot readily do that in a SELECT statement. You need to use dynamic SQL. — Gordon Linoff, Dec 06 '20 at 12:06

score 1 · Accepted Answer · answered Dec 06 '20 at 11:27

You need dynamic sql for that purüose, as you can't do it drectly

CREATE TABLE myTable(i int)

SET @itemname  := 'HELLO WORLD';

    SET @sql := CONCAT('SELECT COUNT(*) AS ', SUBSTRING(@itemname, 1, 5), ' FROM myTable');


    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


| HELLO |
| ----: |
|     0 |

db<>fiddle here

score 0 · Answer 2 · answered Dec 06 '20 at 09:47

0

You can use dynamic SQL :

DECLARE @itemname VARCHAR(50) = 'HELLO WORLD'
SELECT CONCAT('SELECT col1 AS ', SUBSTRING(@itemname, 1, 5), ' FROM myTable');

This outputs

SELECT col1 AS HELLO FROM myTable

Now, you can copy paste that result or EXECUTE it

Here are more informations about the usage of EXECUTE : Is it possible to execute a string in MySQL?

answered Dec 06 '20 at 09:47

Cid

14,968
4
30
45

Thanks for the effort, but I think you misunderstood what I was trying to achieve here. Let me rephrase, I would like to Alias a column name using a SUBSTRING function for a declared item. – swordfish81 Dec 06 '20 at 09:52
I perfectly understood your question, but it would need an extra step to be achieved – Cid Dec 06 '20 at 09:53

SQL - How to use SUBSTRING function to output as Col Name

2 Answers2