I've searched for a bit but wasn't able to find a specific question on this. How do I obtain all the column names in a table whose names contain a specific string? Specifically, if the column name satisfies like %bal% then I would like to write a query that would return that column name and others that meet that criteria in Sybase.
Edit: The Sybase RDBMS is Sybase IQ.
Updated based on OPs additional comments re: question is for a Sybase IQ database.
I don't have a Sybase IQ database in front of me at the moment but we should be able to piece together a workable query based on IQ's system tables/views:
The easier query will use the system view SYSCOLUMNS:
select cname
from SYS.SYSCOLUMNS
where tname = '<table_name>'
and cname like '%<pattern_to_match>%'
Or going against the system tables SYSTABLE and SYSCOLUMN:
select c.column_name
from SYS.SYSTABLE t
join SYS.SYSCOLUMN c
on t.table_id = c.table_id
where t.table_name = '<table_name>'
and c.column_name like '%<pattern_to_match>%'
NOTE: The Sybase ASE query (below) will probably also work since the referenced (ASE) system tables (sysobjects, syscolumns) also exist in SQL Anywhere/IQ products as a (partial) attempt to provide (ASE) T-SQL compatibility.
Assuming this is Sybase ASE then a quick join between sysobjects and syscolumns should suffice:
select c.name
from dbo.sysobjects o
join dbo.syscolumns c
on o.id = c.id
and o.type in ('U','S') -- 'U'ser or 'S'ystem table
where o.name = '<table_name>'
and c.name like '%<portion_of_column_name>%'
For example, let's say we want to find all columns in the sysobjects table where the column name contains the string 'trig':
select c.name
from dbo.sysobjects o
join dbo.syscolumns c
on o.id = c.id
and o.type in ('U','S')
where o.name = 'sysobjects'
and c.name like '%trig%'
order by 1
go
----------
deltrig
instrig
seltrig
updtrig