How do i select columns based on multiple strings?



  • Suppose I have the following column names:

    Name1_a, 
    Name1_b, 
    Name1_c, 
    Name2_a, 
    Val1_a, 
    Val1_b, 
    Val2_a, 
    Var1_a, 
    Var1_b, 
    temp1, 
    temp2, 
    temp3
    

    How do I write an SQL query where I can select columns that start with Name, Val and Var?



  • To get the column names that start with Name, Val and Var you should use information_schema .

    Try:

    select TABLE_SCHEMA,
           TABLE_NAME,
           COLUMN_NAME
    from information_schema.COLUMNS
    where COLUMN_NAME like 'Name%' 
    or COLUMN_NAME like 'Val%' 
    or COLUMN_NAME like 'Var%';
    

    Edit

    There is no table schema, just a single table named 'Table1' with the column names

    TABLE_SCHEMA represent the database in which you can find all the tables that have the columns which start with Name, Val and Var.

    If you want only the table names remove from the select statement,

        select COLUMN_NAME
        from information_schema.COLUMNS
        where COLUMN_NAME like 'Name%' 
        or COLUMN_NAME like 'Val%' 
        or COLUMN_NAME like 'Var%';
    

    If you want specific database name use:

    select COLUMN_NAME
    from information_schema.COLUMNS
    where TABLE_SCHEMA='your_database_name'
    and COLUMN_NAME like 'Name%' 
    or COLUMN_NAME like 'Val%' 
    or COLUMN_NAME like 'Var%';
    

    If you need specific table name and database name use:

    select COLUMN_NAME
    from information_schema.COLUMNS
    where TABLE_SCHEMA='your_database_name'
    and TABLE_NAME ='your_table_name'
    and COLUMN_NAME like 'Name%' 
    or COLUMN_NAME like 'Val%' 
    or COLUMN_NAME like 'Var%';
    

    Check for more info on https://dev.mysql.com/doc/refman/8.0/en/information-schema.html#:%7E:text=INFORMATION_SCHEMA%20provides%20access%20to%20database,data%20dictionary%20and%20system%20catalog.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2