1

I want to search for a single value in 3 columns and return each occurrence of this value. My query is based on the following answer regarding a similar request.

SELECT * 
FROM table 
WHERE 'abc' IN (column1, column2, column3)

However, I don't want to return the whole row, only the single value. It possible that the value can found in multiple columns for the same row. Each occurrence should be returned and the end result should be a 1-dimensional list. How do I need to alter my query?

forpas
  • 160,666
  • 10
  • 38
  • 76
HJA24
  • 410
  • 2
  • 11
  • 33

1 Answers1

1

The value that you want returned in each row is the same as the value that you search for, so a statement like this will do:

SELECT 'abc' AS column_name 
FROM table 
WHERE 'abc' IN (column1, column2, column3)

If you want 'abc' returned once for each occurrence in any of the 3 columns you should use UNION ALL:

SELECT column1 AS column_name 
FROM table 
WHERE column1 = 'abc'
UNION ALL
SELECT column2 AS column_name 
FROM table 
WHERE column2 = 'abc'
UNION ALL
SELECT column3 AS column_name 
FROM table 
WHERE column3 = 'abc'
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thanks, that does the job! If the value must be partial match, I can use `LIKE`. How should I modify your answer in that case? `SELECT 'abc' AS column_name FROM table WHERE (column1, column2, column3) LIKE 'abc'` gives an error "row value misused" – HJA24 Mar 27 '22 at 12:43
  • 1
    @HJA24 You can't use LIKE for many columns like that. Each column must be checked separately: `SELECT 'abc' AS column_name FROM table WHERE column1 LIKE 'abc%' or column2 LIKE 'abc%' or column3 LIKE 'abc%'` – forpas Mar 27 '22 at 12:46