How to Select Columns Dynamically Based on a Wildcarded Prefix in MySQL?

Dynamically Selecting Columns Based on a Wildcarded Prefix

Within a database, one may encounter situations where they need to select specific columns based on a shared prefix in their names. While the provided MySQL query attempt with a wildcard does not work, there is a solution involving dynamically generating the SQL.

To retrieve column names that start with a particular prefix, one can leverage the following SQL query:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Foods'
    AND table_schema = 'YourDB'
    AND column_name LIKE 'Vegetable%'

In this query, 'Foods' represents the table name, 'YourDB' is the database schema, and 'Vegetable' is the desired column prefix. Replace these values with your specific table, schema, and prefix.

Once the column names are obtained, you can dynamically construct a query to select the corresponding data. For example:

SELECT `VegetableName1`, `VegetableName2`, `VegetableName3`
FROM `Foods`

Here, assume the column names obtained are 'VegetableName1', 'VegetableName2', and 'VegetableName3'. Replace these names with the ones derived from the above INFORMATION_SCHEMA query.

By employing this approach, you can effectively select specific columns based on their shared wildcard prefix. This technique is useful when working with databases where column names follow consistent naming conventions.

The above is the detailed content of How to Select Columns Dynamically Based on a Wildcarded Prefix in MySQL?. For more information, please follow other related articles on the PHP Chinese website!