SQL: select where either column is matched

Question

I'll illustrate my problem:

Look at the table with two columns: Col1 and Col2.

-----------------   
| Col1  | Col2  |
-----------------
| 1a1   | 2b2   |
-----------------
| 11b   | 22a   |
-----------------

I am trying to select values that contain an a in either column.

The desired output would be:

------------
| MyNewCol |
------------
| 1a1      |
------------
| 22a      |
------------ 

Thank you in advance for your time.

Answer 1

select col1 as newCol
from your_table
where charindex('a', col1) > 0
union all
select col2
from your_table
where charindex('a', col2) > 0

Answer 2

Try this:

SELECT col1 AS output_col
FROM your_table
WHERE CHARINDEX('a', col1, 1) > 0
UNION ALL
SELECT col2
FROM your_table
WHERE CHARINDEX('a', col2, 1) > 0

It finds records where either value contains 'a' and performs a UNION ALL. If there are more than 2 columns, a different approach may be needed, as having a lot of UNIONs may cause performance issues.