0

I have a query, which has around 50 columns and it is subject to change.

I need to modify my SQL-DML to search a specific string across all 50 columns.

How is this possible?

select
  *
from
  messages a
  FULL OUTER JOIN  customers b ON a.customer_id = b.customer_id
  FULL OUTER JOIN orders c ON c.customer_id = b.customer_id
  FULL OUTER JOIN delivery_templates d ON c.billing_template_id = d.delivery_template_id

where
  product_id != '0'
  and a.hide_question = '0'
  and a.seller_id in ('2', '3', '1', '6')
  AND a.market_place_id IN (
    SELECT
      MAX(market_place_id)
    FROM
      messages
    GROUP BY
      product_id
  )
order by
  a.market_place_id desc

So far the best alternative seem to be

SELECT * 
FROM MyTable 
WHERE Col1 LIKE '%foo%' OR 
      Col2 LIKE '%foo%' OR  
      Col3 LIKE '%foo%' OR 
      Col4 LIKE '%foo%' OR 
      Col5 LIKE '%foo%' OR 
      Col6 LIKE '%foo%'
1

A database is not a fancy mechanism to grep. This is a horrible idea don't do this ever. That said you can convert the row object to a tsvector using Full Text Search

SELECT to_tsvector(to_jsonb(t)) @@ 'bar'
FROM ( VALUES ('foo','bar','baz') ) AS t(a,b,c); 

or you can search it as if it was text.

SELECT t::text LIKE '%bar%'
FROM ( VALUES ('foo','bar','baz') ) AS t(a,b,c); 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.