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


 
Thread Tools Search this Thread
Top Forums Programming Query to SELECT only Column Names that Contain a Specific String?
# 1  
Old 12-06-2013
Query to SELECT only Column Names that Contain a Specific String?

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...
Code:
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  
Old 12-10-2013
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
This User Gave Thanks to rbatte1 For This Post:
# 3  
Old 12-11-2013
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...
Code:
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...
Quote:
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%' AND t.name = 'Table_Name_I_Want'
Thanks Again,
Matt

Last edited by mrm5102; 12-11-2013 at 12:44 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Change some string in specific column with space

Hello All of Master Script , i need help to solve my problem before : mount /dev/rdsk/c1t69d0s6 /dev/rdsk/c1t69d0s6 /vol/cl123/PURGE1 ufs mount /dev/rdsk/c1t70d0s6 /dev/rdsk/c1t70d0s6 /vol/cl123/PURGE2 ufs expected : mount /dev/dsk/c1t69d0s6 /dev/rdsk/c1t69d0s6 /PURGE1 ufs mount ... (3 Replies)
Discussion started by: k0p0nkkk
3 Replies

2. Shell Programming and Scripting

Overwrite specific column in xml file with the specific column from adjacent line

I have an xml file dumped from rrd file, that I want to "patch" so the xml file doesn't contain any blank hole in the resulting graph of the rrd file. Here is the file. <!-- 2015-10-12 14:00:00 WIB / 1444633200 --> <row><v> 4.0419731265e+07 </v><v> 4.5045912770e+06... (2 Replies)
Discussion started by: rk4k
2 Replies

3. Shell Programming and Scripting

Replace specific column range in a non-delimited file with a string!

Hi All, I will need an help with respect to replacing a range of columns on a non-delimited file using a particular string pattern. Say file input is MYNUMBERD000000-BAN CHUE INSNTS ** N+ MYAREDSDD000000+BAN CHUE INSNTS ** N+ MYDERFFFSD00000-GIR PENT - ACH ** ... (5 Replies)
Discussion started by: navojit dutta
5 Replies

4. Shell Programming and Scripting

Delete column with exact string in specific col

Hi, my file structur looks like File structure looks: GeneID protein_gi Symbol 1246500 10954455 repA1 1246501 10954457 repA2 1246502 10954458 leuA But some of the cases do not have record for protein id. for example: 1343044 - orf01 I want to remove those rows. But I tried awk... (9 Replies)
Discussion started by: smitra
9 Replies

5. Shell Programming and Scripting

Print unique names in a specific column using awk

Is it possible to modify file like this. 1. Remove all the duplicate names in a define column i.e 4th col 2. Count the no.of unique names separated by ";" and print as a 5th col thanx in advance!! Q input c1 30 3 Eh2 c10 96 3 Frp c41 396 3 Ua5;Lop;Kol;Kol c62 2 30 Fmp;Fmp;Fmp ... (5 Replies)
Discussion started by: quincyjones
5 Replies

6. Shell Programming and Scripting

Print unique names in each row of a specific column using awk

Is it possible to remove redundant names in the 4th column? input cqWE 100 200 singapore;singapore AZO 300 400 brazil;america;germany;ireland;germany .... .... output cqWE 100 200 singapore AZO 300 400 brazil;america;germany;ireland (4 Replies)
Discussion started by: quincyjones
4 Replies

7. Shell Programming and Scripting

Select a specific part of the string and print it

Hi all, I have a string that looks like: #!/bin/sh options="arguments: --user=alpha --group=beta --prefix=/usr/share --proxy-path=/proxy --proxy-tmp=/tmp --conf-path=/etc" My goal is to transform the string into an array, then for each key, if it starts with "--proxy" to print the string... (2 Replies)
Discussion started by: TECK
2 Replies

8. UNIX for Dummies Questions & Answers

How to remove a string from a specific column in a file

Hello, A basic query. How can I remove a string from a specific column. For example, remove "abcd" just from column 2 in example file: abcd abcd1 abcd abcd2 abcd abcd3 to get output: abcd 1 abcd 2 abcd 3 Thank you!:) (4 Replies)
Discussion started by: auburn
4 Replies

9. UNIX for Dummies Questions & Answers

replacing string in a column on a specific line

hi, i currently have a file with columns similar to this customer name owed CID123 John 300 CID342 harry 500 at present i use use awk to find the amount owed by the customer using the customer ID (CID). if the customer spends more money how would i go about using sed/awk etc to... (2 Replies)
Discussion started by: skinnygav
2 Replies

10. UNIX for Advanced & Expert Users

How to select only those file names whose name contains only numbers

Hi Guru's, Before writing to this forum I have searched extensively on this forum about my problem. I have to write a shell script which takes out only those file names from the given directory which contains only numbers. For example, In the given directory these files are present: ... (4 Replies)
Discussion started by: spranm
4 Replies
Login or Register to Ask a Question

Featured Tech Videos