如何在含30+字段的表中快速筛选列名以'Flag'开头的字段？

AI 小北

2026-5-21

How to Select Columns Starting with 'Flag' in Your Table

Hey Kevin, let's get that query sorted out—your current syntax is mixing up row filtering with column selection, which is why it's not working. Here's how to properly grab all columns starting with 'Flag' from your Table1:

The Core Issue

Your original query select * Like Flag% from Table1 uses LIKE incorrectly—LIKE is for filtering rows based on data values, not for picking specific columns by their names. To target columns by name, we need to query your database's system metadata tables first.

Step-by-Step Solutions by Database

Below are tailored methods for common databases:

MySQL/MariaDB

First, fetch all matching column names:

SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name' -- Replace with your actual database name
  AND TABLE_NAME = 'Table1'
  AND COLUMN_NAME LIKE 'Flag%';

Copy the result of this query, then paste it into a SELECT statement:

SELECT FlagColumn1, FlagColumn2, ... FROM Table1;

Or use dynamic SQL to run it in one go:

SET @cols = (SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ')
             FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = 'your_database_name'
               AND TABLE_NAME = 'Table1'
               AND COLUMN_NAME LIKE 'Flag%');
SET @query = CONCAT('SELECT ', @cols, ' FROM Table1');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server

Fetch matching columns:

SELECT STRING_AGG(name, ', ')
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'Table1'
  AND c.name LIKE 'Flag%';

Then build your final query with the returned column list, or use dynamic SQL:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SELECT @cols = STRING_AGG(name, ', ')
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'Table1'
  AND c.name LIKE 'Flag%';
SET @query = N'SELECT ' + @cols + N' FROM Table1';
EXEC sp_executesql @query;

PostgreSQL

Get your column list:

SELECT string_agg(column_name, ', ')
FROM information_schema.columns
WHERE table_schema = 'public' -- Replace with your schema if it's not public
  AND table_name = 'Table1'
  AND column_name LIKE 'Flag%';

Or use dynamic SQL with EXECUTE:

DO $$
DECLARE
  cols TEXT;
BEGIN
  SELECT string_agg(column_name, ', ') INTO cols
  FROM information_schema.columns
  WHERE table_schema = 'public'
    AND table_name = 'Table1'
    AND column_name LIKE 'Flag%';
  EXECUTE 'SELECT ' || cols || ' FROM Table1';
END $$;

Key Takeaway

Since you can't directly filter columns in the SELECT clause with LIKE, leveraging your database's metadata tables is the reliable way to get the exact columns you need—especially handy when dealing with tables that have 30+ fields!

内容的提问来源于stack exchange，提问作者Kevin Dion