0

I want to be able to search through entire table without specifying each column. I want to be able to look for a record where each column may look LIKE '%QASHQAI%'. This table has about 100 different columns. Naming all of them like

SELECT * FROM vehicle WHERE ... LIKE '%QASHQAI%' OR ... LIKE '%QASHQAI%' OR ... LIKE '%QASHQAI%';

Is there a quicker way of searching through the table?

hellomate
  • 133
  • 1
  • 1
  • 8
  • 3
    100 similar columns indicates it's time to redesign your database. (Google normalization.) – jarlh Jan 12 '18 at 15:47
  • As far as I know, it's not possible to search all columns of a table for a value without referencing all of the column names that you want to search in some way in the query. – Bibin Mathew Jan 12 '18 at 15:50
  • 1
    Have a look here : [I want Search specific value in all columns of all tables in oracle 11g](https://stackoverflow.com/questions/5193828/i-want-search-specific-value-in-all-columns-of-all-tables-in-oracle-11g) or here: [Search All Fields In All Tables For A Specific Value (Oracle)](https://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle) – Valerica Jan 12 '18 at 15:51
  • 2
    If you don't want to type the name of every column in your query, you will need to use dynamic SQL. Dynamic SQL should be a last resort, not the first thing you try; and besides, it is an advanced technique that may well be above your current skill level. More importantly though, as jarlh mentioned, the problem statement suggests there is something exceptionally wrong with your table design to begin with. Why would you look for all the rows where either the name, the address, or the product code contains "Meredith"? That makes no sense. –  Jan 12 '18 at 15:53
  • @mathguy I wasn't responsible for creation of this table however, it contains similar values for example one columns is for model, other for make, another for make and model combined (nobody knows why). The stack grows... I wanted to quickly find a column with a record that is going to start with QASHQAI and not have NISSAN at the beginning and will not have anything after like engine size. Some columns look like 'NISSAN QASHQAI HATCHBACK 2.0 blablabla' – hellomate Jan 12 '18 at 16:25
  • In my experence, i created a view, which had 1 column general all column. and i search in this column. – T.Q.H Jan 15 '18 at 10:17
  • @T.Q.H Then I wouldn't know what column the record comes from. – hellomate Jan 16 '18 at 10:12
  • To find a collumn that starts with a certain name you could maybe use `SELECT Column_Name FROM user_tab_columns WHERE Table_Name = UPPER('vehicle ') AND Column_Name LIKE '%QASHQAI%';`. – Tenzin Jan 16 '18 at 10:16
  • It's not about column name, it's about what's inside that column. I want to be able to find record that starts with 'QASHQAI%' in all columns. – hellomate Jan 16 '18 at 11:58

1 Answers1

0

i think it s not possible with a simple query but you can solve this with a stored procedure. see my comments here

aljassi
  • 246
  • 2
  • 10