Selecting All Columns That Start with Xxx Using a Wildcard

Selecting all columns that start with XXX using a wildcard?

There's no way to do exactly what you're trying to. You could do another query first to fetch all the column names, then process them in PHP and build the second query, but that's probably more complex than just writing out the names that you want.

Or is there a reason this query needs to be dynamic? Will the table's structure change often?

SQL Column Name wildcard

You will want to build a dynamic query as explained here: https://stackoverflow.com/a/4797728/9553919

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

select column names that start with x

Try this

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_NAME`='producten' AND `COLUMN_NAME` LIKE 'pweb%'
AND DATA_TYPE = 'int'

Dynamic SQL Column Selection

You can use dynamic unpivot with the columns which you already selected. Following sample query might help you.

DECLARE 
  @sql  NVARCHAR(MAX) = N'',
  @cols NVARCHAR(MAX) = N'';

SELECT @cols += ', ' + QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('yourtable')
  AND name LIKE '2020%';

SET @cols =  STUFF(@cols, 1, 1, '')

SELECT @sql = N'SELECT Other_columns, date_columns, val
  FROM 
  (
    SELECT Other_columns, '  + @cols + '
    FROM yourtable
  ) t
  UNPIVOT
  (
    val FOR date_columns IN (' + @cols + ')
  ) up;';
PRINT @sql;
-- EXEC sp_executesql @sql;

Please find the final db<>fiddle here.

Find column whose name contains a specific string

Just iterate over DataFrame.columns, now this is an example in which you will end up with a list of column names that match:

import pandas as pd

data = {'spike-2': [1,2,3], 'hey spke': [4,5,6], 'spiked-in': [7,8,9], 'no': [10,11,12]}
df = pd.DataFrame(data)

spike_cols = [col for col in df.columns if 'spike' in col]
print(list(df.columns))
print(spike_cols)

Output:

['hey spke', 'no', 'spike-2', 'spiked-in']
['spike-2', 'spiked-in']

Explanation:

1. df.columns returns a list of column names
2. [col for col in df.columns if 'spike' in col] iterates over the list df.columns with the variable col and adds it to the resulting list if col contains 'spike'. This syntax is list comprehension.

If you only want the resulting data set with the columns that match you can do this:

df2 = df.filter(regex='spike')
print(df2)

Output:

   spike-2  spiked-in
0        1          7
1        2          8
2        3          9

Select all columns except one in MySQL?

Actually there is a way, you need to have permissions of course for doing this ...

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_omit>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Replacing <table>, <database> and <columns_to_omit>

---

Related Topics