CODEWITHSUNDEEP
sqlpostgresql
Arun
Arun

Reputation: 11

Is it possible to search the table on all columns for word occurence, without repeating like operator?

I want to search for a word in occurrence in the table. In my table there are 80 columns available. Is there any possibilities are there to search for the a word without repeating the like operator for all the columns. I tried to figure this out in the net, but i cannot find the solution. thanks in advance.

Upvotes: 0

Views: 95

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324455

This almost certainly means you have major design problems in your schema and need to look at further normalization.

If it makes sense to search for a value over 80 columns... well, yeah, re-think your schema.

In theory you can do:

SELECT *
FROM the_table
WHERE the_table::text LIKE '%word%';

which converts the whole tuple to its text form and then searches it. This is in practice a terrible idea, though; it'll include all columns, not just the text columns you want, it'll add quotes and other formatting for the row literal, and it won't tell you which column(s) the word appeared in.

The saner approach would usually be to use in-database full-text search, which supports multi-column searches. Over 80 columns that's going to be awkward because you do have to repeat all the columns, but it's the only sane way to proceed. If it's too much hassle, you probably need to fix your schema design.

Upvotes: 4

Jeet Bhatt
Jeet Bhatt

Reputation: 778

Try this,

Below query based on Columnwise.

select COUNT(*) from dbo.TableNAme
where PatIndex('searchword',ColumnName) > 0

Upvotes: -2

Related Questions