Visit The New, Modern Unix Linux Community


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  
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  
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  
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..

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #1003
Difficulty: Medium
In the 1970s, Grace Hopper, also known as "Grandma COBOL", pushed for the Defense Department to replace large, centralized systems with networks of small, distributed computers.
True or False?

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

Featured Tech Videos