1

I did some searching and from one question already posted on stackexchange, the answer was that it was not possible, but I figured to ask. I did not know if it was possible to form a SELECT query to dynamically select which columns will be displayed in a mysql SELECT statement result. Example:

Say I have column names Person, ID, Phone Number, Alt Number for this table:

John | 79 | 800-499-0000 | 800-499-5555

I would like to form a SELECT statement so that it will only pull down columns where string '800-499' is somewhere in the field. Thus the result from MySQL ideally would be:

800-499-0000 | 800-499-5555

The only problem is that I do not think dynamically selecting columns is possible.

Any help or confirmation is appreciated.

jcsbrotha
  • 89
  • 3
  • 15
  • 1
    No it's not possible to have variable columns in a resultset. May I ask what problem are you trying to solve with it? – Quassnoi Apr 25 '13 at 16:34
  • well there is a massive table with a ton of columns so getting to the fields I need is a pain. It would be helpful to be able to only get the columns that carry a substr of information so that I dont have to run through all of the other columns to find the ones I want. – jcsbrotha Apr 25 '13 at 16:36
  • 1
    @jcsbrotha: But what if the matching columns differ from record to record? – eggyal Apr 25 '13 at 16:37
  • @jcsbrotha: what do you mean by "run through the columns"? – Quassnoi Apr 25 '13 at 16:38
  • @eggyal, yes and I recognize that could be an issue, but was trying to accomplish it by just pulling down one row which would avoid that problem. I realize it is not liekly but figured I would ask those smarter and wiser than I. – jcsbrotha Apr 25 '13 at 16:39
  • @Quassnoi by "run through the columns" I mean it searches through the values of the fields for each column to see if it can find the substr and return that field specifically – jcsbrotha Apr 25 '13 at 16:40
  • 2
    If it's *possible* for such data to exist in many columns of the same table, you probably need to normalise your schema. – eggyal Apr 25 '13 at 16:42
  • @jcsbrotha: what is "it" which searches through the columns? – Quassnoi Apr 25 '13 at 16:42
  • @Quassnoi sorry I did not apply the phrase to the appropriate part. Run through all of the columns means I have to scroll from left to right in the result set to try and find all the fields where that SUBSTR occurs. – jcsbrotha Apr 25 '13 at 16:43
  • @eggyal well while I do agree with your premise, in this case it is not applicable. If you notice from the example I gave, the "data" is not identical in each column but carries the same SUBSTR of "800-499". Thus my problem could not be normalized as proposed. – jcsbrotha Apr 25 '13 at 16:45
  • @jcsbrotha: I'm afraid I must beg to differ. In the given example, one might consider moving all telephone numbers to another table `(ID, Number, Type)` containing e.g. `(79, '800-499-0000', 'Phone')` and `(79, '800-499-5555', 'Alt')`. Then your query becomes a case of simply selecting from this new table `WHERE Number LIKE '800-499-%'`. – eggyal Apr 25 '13 at 16:48
  • @eggyal please keep in mind this was only an example. The reality is that I have a monster table (which probably should be redone but unfortunately in part I inherited it from one of the owners of the company and lots of customers using it so not easily done) with lots of different columns and a variation of information but some fields have remnants of others. Example one field may be `800-499-0000` but another field will be `mynum/800-499-0000` and another field will be `800-499-0000@domain`, but other fields will just be `John Doe` or `1`, etc. I hope my conundrum is clearer now. – jcsbrotha Apr 25 '13 at 17:10
  • @jcsbrotha: I still maintain that the schema ought to be normalised. – eggyal Apr 25 '13 at 17:27
  • @eggyal Well I definitely do appreciate the input and taking the time to delve into the issue with me. I appreciate all of the comments from everyone here. – jcsbrotha Apr 25 '13 at 17:29
  • Normalize your table. – Kermit Apr 25 '13 at 17:48

1 Answers1

1

You could try something like:

select * from
(select concat(case when col1 like '%800-499-0000%' then concat('col1: ',col1,';') end,
               case when col2 like '%800-499-0000%' then concat('col2: ',col1,';') end,
 ...
               case when coln like '%800-499-0000%' then concat('coln: ',coln,';') end)
        as search_results
 from my_table) sq
where search_results is not null
  • This is the kind of query that makes a DBA cry. Using a search engine like [Solr](http://lucene.apache.org/solr/) would benchmark about a kabillion times better. – tadman Apr 25 '13 at 16:45
  • Short answer: [Yes](http://stackoverflow.com/questions/4604393/using-solr-with-mysql). – tadman Apr 25 '13 at 16:49
  • @tadman: Longer answer (from the linked question) - when used with the data import handler. This may be a viable solution to the OP's question - do you want to add it as an answer? –  Apr 25 '13 at 16:53
  • If you want to figure out how it applies, all yours. – tadman Apr 25 '13 at 19:11