在所有列中使用筛选器的ORACLE SQL查询

31moq8wy 于 2022-12-11 发布在 Oracle

关注(0)|答案(1)|浏览(126)

SELECT * 
FROM TABLE 
WHERE COLUMN_NAME LIKE = '%ABC%'

This query will search for a string in any column and return the result. But I want to get all rows which contain that string in any of the columns.
Is there any way to get the output after filtering in all the columns?
NOTE: I have a table which has 50 columns, so using a for loop and hitting the database 50 times (one for each column) is not time efficient.

oracle

来源：https://stackoverflow.com/questions/74697079/oracle-sql-query-with-filter-in-all-columns

1条答案

按热度按时间

tjvv9vkg1#

We can concat the strings of those columns and apply the condition on this entire string:

SELECT column1, column2, column3
FROM yourtable
WHERE 
column1 || column2 || column3 
LIKE '%ABC%';

Of course, there are some possible edge cases that this would fetch rows where for example column1 has a string 'AB' and column2 a string 'CD' and such rows should not be selected. If this is the case, we will need to use OR instead.

SELECT column1, column2, column3
FROM yourtable
WHERE 
column1 LIKE '%ABC%' 
OR column2 LIKE '%ABC%'
OR column3 LIKE '%ABC%';

Or we can follow the first option and add some separator between the columns:

SELECT column1, column2, column3
FROM yourtable
WHERE 
column1 || 'S' || 
column2 || 'S' ||
column3 
LIKE '%ABC%';

In my opinion, all these options are unpleasant. That's because pure SQL is not good in such things.
I recommend to do this in your application rather than as pure SQL query. Or you could at least find a complete string to search in the columns to avoid the LIKE . Then you could do this:

SELECT column1, column2, column3
FROM yourtable
WHERE 
'yourString' IN (column1, column2, column3);

赞(0）回复(0）举报 2022-12-11

我来回答

在所有列中使用筛选器的ORACLE SQL查询

1条答案

相关问题

热门标签

最新问答