SQL: Select data from table where table's column name starts with alphabet

Kumar, Thrainder (MIND) 176 Reputation points
2021-04-25T20:46:20.763+00:00

Hi,

I have a table named Test_tbl with about 50 columns in it. 25 columns name start with "INFO_" and rest 25 with "DTL_".
In my project, I use this table 100 times but with the dynamic query to select or update records by mentioning each column name every time.

Is there any way to put the like condition on the column's name of a table. So, we can manage this kind of data/table structure easily.

Something like this:

SELECT COLUMN_NAME LIKE 'INFO_%'
FROM TEST_TBL

or

SELECT * FROM TEST_TBL
WHERE INFORMATION_SCHEMA.COLUMN_NAME LIKE 'INFO_%'
Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,405 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,532 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-04-26T03:18:36.02+00:00

    Hi @Kumar, Thrainder (MIND)

    Please try:

        --Check whether there is an object with the same name in the database, delete if there is  
        IF(exists(SELECT * FROM sysobjects WHERE name='yourtable'))   
        DROP TABLE yourtable;  
        IF(exists(SELECT * FROM sysobjects WHERE name='Fuzzy_search1'))   
        DROP PROC Fuzzy_search1;  
          
        --Create test tables and stored procedures  
        CREATE TABLE yourtable(INFO_aa int,INFO_bb int,INFO_cc int,  
        DTL_aa int,DTL_bb int,DTL_cc int);  
          
        CREATE PROC Fuzzy_search1(@Dup NVARCHAR(max)='INFO')  
        AS  
        DECLARE @sql NVARCHAR(MAX)  
        DECLARE @col NVARCHAR(MAX)  
        SELECT @col =STUFF(( SELECT ',' + t.name FROM   
                                (SELECT name  
                                 FROM syscolumns  
                                 WHERE id=(SELECT MAX(id)  
                                           FROM sysobjects  
                                           WHERE xtype = 'u'  
                                           AND name = 'yourtable')) t  
                              WHERE reverse(substring(reverse(t.name),charindex('_',reverse(t.name)) +1,500))=@Dup  
                              FOR XML PATH('')  
                             ), 1, 1, '')  
          
        SET @sql = 'SELECT  ' + @col + ' FROM yourtable';  
        EXEC(@sql);   
          
        --Execute stored procedure  
        EXEC Fuzzy_search1 'INFO'  
    

    Output:
    91067-image.png

    IF(exists(SELECT * FROM sysobjects WHERE name='Fuzzy_search2'))   
    DROP PROC Fuzzy_search2;  
      
    CREATE PROC Fuzzy_search2(@Dup NVARCHAR(max)='DTL')  
    AS  
    DECLARE @sql NVARCHAR(MAX)  
    DECLARE @col NVARCHAR(MAX)  
    SELECT @col =STUFF(( SELECT ',' + t.name FROM   
                            (SELECT name  
                             FROM syscolumns  
                             WHERE id=(SELECT MAX(id)  
                                       FROM sysobjects  
                                       WHERE xtype = 'u'  
                                       AND name = 'yourtable')) t  
                          WHERE reverse(substring(reverse(t.name),charindex('_',reverse(t.name)) +1,500))=@Dup  
                          FOR XML PATH('')  
                         ), 1, 1, '')  
      
    SET @sql = 'SELECT  ' + @col + ' FROM yourtable';  
    EXEC(@sql);   
      
    EXEC Fuzzy_search2 'DTL'  
      
    DROP TABLE yourtable  
    DROP PROC Fuzzy_search1  
    DROP PROC Fuzzy_search2  
    

    Output:
    91124-image.png

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-04-25T21:39:25.397+00:00

    No, there is not. The best you can do is to define views that only includes the INFO columns. Well, there is one more option, which is really esoteric. You can defines INFO_ columns as sparse column, and then you can define a column set for all sparse column, and that column set will give you all sparse columns in a single XML document. Yuk.

    0 comments No comments