'PostgreSQL: finding multiple columns that match a like clause

I have a simple table:

column 1               column2               column3
 AA                     AAAA                    TT                
 AA                     BB                      EE
 AB                     C                       AABBCC 
 ABC                    XXX                     XYZ 
 AABB                   YYY                      A

now i tried to find all columns, that match e.g. 'AA%' resulting in:

column 1               column2               column3
 **AA**                **AAAA**                null                 
**AA**                  null                   null    
 null                   null                  *AABBCC* 
 null                   null                   null        
 **AABB**               null                   null

is that possible with one simple query? my idea was to start with unions or temporay tables, but i can't get it to work. sorry for this simple beginner question and thanks in advance!

select d1.column1, d1.column2, d1.column3 from sample d1 where d1.column1 like 'AA%' 
   union 
      select d2.column1, d2.column2, d2.column3 from sample d2 where d2.column2 like 'AA%' 
         union 
            select d3.column1, d3.column2, d3.column3 from sample d3 where d3.column3 like 'AA%'
sqlpostgresqlsql-like


Solution 1:[1]

You could just use OR key-word as this:

SELECT column1, column2, column3 
FROM sample 
WHERE column1 like 'AA%' 
OR column2 like 'AA%' 
OR column3 like 'AA%'

Solution 2:[2]

You union query is also correct bt in case of simplicity i agree with Or query

Solution 3:[3]

With PostgreSQL there is the ANY or ALL:

WHERE col LIKE ANY( subselect )

or

WHERE col LIKE ALL( subselect )

where the subselect returns exactly one column of data.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 LaurentY
Solution 2 Masterpiece.beta
Solution 3 conol

Related Questions