question

KumarThrainderMIND-0302 avatar image
0 Votes"
KumarThrainderMIND-0302 asked EchoLiu-msft commented

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

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_%'



sql-server-generalsql-server-transact-sqlazure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please have any updates?

Regards
Echo

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @KumarThrainderMIND-0302

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.



image.png (981 B)
image.png (845 B)
image.png (948 B)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.