bigquery select all columns that contain string-CodePudding

How to select all columns with that contain a string? Either prefix, suffix or containing.

Column names
Available columns : a_test, b_test, c_test, d_test, e, f, g, test_h, test_i
Selected Prefix columns : a_test, b_test, c_test, d_test
Selected Suffix columns : test_h, test_i
Selected all test columns : a_test, b_test, c_test, d_test, test_h, test_i

CodePudding user response：

Assuming you only want to search in tables, start with this:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE (C.COLUMN_NAME LIKE @prefix   '_'   'test' 
       OR C.COLUMN_NAME LIKE 'test'   '_'   @suffix )
AND C.TABLE_NAME = 'MYTABLENAME'

...and change the "WHERE" condition according to your needs.

Edited: no INNER JOIN and changed where condition.