PostgreSQL: finding multiple columns that match a like clause

Question

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%'

LaurentY · Accepted Answer · 2015-03-11 10:53:48Z

4

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%'

answered Mar 11, 2015 at 10:53

LaurentY

7,7193 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Masterpiece.beta · Accepted Answer · 2015-03-16 15:49:00Z

0

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

answered Mar 16, 2015 at 15:49

Masterpiece.beta

1112 bronze badges

Comments

conol · Accepted Answer · 2021-05-20 09:06:40Z

0

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.

answered May 20, 2021 at 9:06

conol

4254 silver badges12 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: finding multiple columns that match a like clause

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related