oracle 当我不确定特定列时，根据字符串匹配过滤行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30359828/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Filter rows according to a string match, when i am unsure of specific column
提问by Srinivas Rao
Can someone help me on SQL Query to filter rows according to a string.
有人可以在 SQL Query 上帮助我根据字符串过滤行吗？
Consider i want to display all rows which contains a string = 'PARIS'. No speicific column to mention. Need filtering at entire row level.
考虑我想显示包含字符串 = 'PARIS' 的所有行。没有特定的专栏可以提及。需要在整个行级别进行过滤。
Is it possible?
是否可以？
Thanks in advance.
提前致谢。
回答by Timo
This is possible indeed, you can have multiple conditions in your WHEREclauseand link them with OR.
这确实是可能的，您可以在WHERE子句中包含多个条件并将它们与OR.
SELECT * FROM yourtable
WHERE yourtable.column1 = 'yourstring'
OR yourtable.column2 = 'yourstring'
This will return all rows from table yourtablewhere column1or column2is equal to yourstring. You can expand this to as many columns as you need.
这将返回表yourtable中column1或column2等于 的所有行yourstring。您可以根据需要将其扩展到任意数量的列。
Note that if you want to look for fields that containyour string, you have to use the LIKEsyntax with wildcards:
请注意，如果要查找包含字符串的字段，则必须使用LIKE带通配符的语法：
SELECT * FROM yourtable
WHERE yourtable.column1 LIKE '%yourstring%'
OR yourtable.column2 LIKE '%yourstring%'
Edit:If you do not know up front which columns are in your table, the only way to solve this that I am aware of would be to fetch the column names using SHOW COLUMNSand build your query dynamically:
编辑：如果您事先不知道您的表中有哪些列，我所知道的解决此问题的唯一方法是使用SHOW COLUMNS并动态构建查询来获取列名：
SHOW COLUMNS FROM yourtable;
After you have the column names, you can assemble the SELECTstatement as described above with multiple WHEREconditions.
获得列名后，您可以SELECT使用多个WHERE条件组合上述语句。
Be aware however, that this might result in problems, as you might end up searching in id columns etc. as well which should probably not be included from a business perspective. Or even worse, you might end up giving information to the end user that he/she is not supposed to have.
但是请注意，这可能会导致问题，因为您最终可能会在 id 列等中进行搜索，从业务角度来看，这些列可能不应该包括在内。或者更糟糕的是，您最终可能会向最终用户提供他/她不应该拥有的信息。
Bottom line, think long and hardabout whether you really cannot find out the names of the columns beforehand.
底线，认为漫长而艰难的是否你真的无法找出列的名称预先。
回答by Mat Richardson
There's no specific syntax to do this as far as I am aware. You could build your query like this:-
据我所知，没有特定的语法可以做到这一点。您可以像这样构建查询：-
DECLARE @YOURSTRING NVARCHAR(50)
SET @YOURSTRING = 'PARIS'
SELECT * FROM YOURTABLE
WHERE COLUMN1 LIKE '%' + @YOURSTRING + '%'
OR COLUMN2 LIKE '%' + @YOURSTRING + '%'
OR....etc...etc..
