Query to SELECT only Column Names that Contain a Specific String?

Applications Programming
1

Hey Guys,

I'm using SQuirreL SQL v3.5 GUI to fetch some data that I need for something I'm working on. I'm also using the IBM Informix Driver (*Version 3.5) to connect to the Database.

What I want to do, if it's even possible, is to show all COLUMNS if they contain the word "Email".

So in Pseudo basically this:
*I know this isn't correct, but just so you have an idea of what I'm trying to do...

SELECT * from db.MyDatabase where COLUMN_NAMES like "%Email%"

So basically I want to see ANY column that contains the word "Email"...

Is this possible? Any thoughts or suggestions would be much appreciated, I am by no means a Database Admin and barley a novice, I really only know what I learned back in college...

Thanks in Advance,
Matt

2

You will probably have to run two commands. One to read the table definition and then build the second one to perform the select you really want.

I put the following into a search engine and got a few results:-

informix command table columns

I hope that this helps,
Robin
Liverpool/Blackburn
UK

1 Like
3

Hey Robin, thanks for the reply!

Sorry for the delay in my reply, the weather has been pretty crazy here the last few days...

Thanks, I used your keyword's search and found a few things to try, but still having a little trouble. I modified the Google search
a bit and found an example of someone trying to list ALL tables where they have column names with a certain string. Which is
pretty close to what I'm trying, but I'm just looking to search within one Table and not the whole DB.

This is the example I found. It lists all Tables and their column names that are LIKE "%string%". So how can I modify this to
search only within one Table instead of the whole DB. I only know the very basics of DB Querying so I'm not really sure...

SELECT c.name AS ColName, t.name AS TableName 
FROM sys.columns c 
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name like '%email%'

So that gives me a list of Column Names containing the string "email" and their corresponding TableName they are in... Any
idea how I can modify it to display just one Table and list column names and their data where the colname contains the string "email"..?

Any suggestions would be much appreciated!

EDIT:
After playing a bit with the given SQL Query above, I was able to add the part below in blue to include ONLY the table I want, but I'm still
trying to figure out how to include the data for those columns as well...

Thanks Again,
Matt