I have inherited a database that has some tables that are wide (60-80 columns each). Many of these columns are due to the original designers adding in user-defined field functionality. They were kind enough to start all of these columns with UDFy_xxxx, where y can be D, C, N (Date, Character, Number, respectively).
I have a request to generate some queries from these columns. My question is whether there is a way to perform a regular expression against the column name in the select statement to return a dynamic set of columns. I am trying to avoid using dynamic SQL.
e.g. Select
I have tried to do a cursor operation to query column names from user_tab_cols, but still couldn’t come up with a clean way of adding these values into the column definition.
Any help would be appreciated.
Regards,
Frank
