CODEWITHSUNDEEP
oracle11g
user3714462
user3714462

Reputation: 1

Query to search a particular string in single table in oracle 11g

I need a query in oracle 11g that will search all the columns of a table for a particular string and give the result.

I have tried a query given below and it worked for me...

SELECT * FROM account
WHERE ACCOUNT_ID like'%gaurav%'
OR    ACCOUNT_NAME like'%gaurav%'
OR    PARENT_ACCOUNT like'%gaurav%'
OR WEBSITE LIKE '%gaurav%'
OR TYPE LIKE'%gaurav%'
  OR   DESCRIPTION LIKE'%gaurav%'
 OR    ACCOUNT_OWNER LIKE'%gaurav%'
  OR    PHONE LIKE'%gaurav%'
OR    STD_CODE LIKE'%gaurav%'
OR    EMPLOYEES LIKE'%gaurav%';

but I need a more simplified solution...as I am having only 10 columns in my table so this solution is okay but what if I have 30-40 columns in my table.

Upvotes: 0

Views: 170

Answers (2)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

If you need a solution that is generic, repeatable, and simple enough to use, then implement a table function with

  • input parameters of: "table name", "searched string"
  • result of: collection of tuple {"rowid", "column name with the match"}

Inside the table function you can implement the functionality you need via means of dynamic SQL.

As for the terms used above ...

Upvotes: 1

bob_saginowski
bob_saginowski

Reputation: 1429

If you have a table with 30-40 columns you should normalize the database: http://www.studytonight.com/dbms/database-normalization.php and you might not need to check all columns (phone for example). Your solution is fine :)

Upvotes: 1

Related Questions