SQL 选择 first_name 其中 last_name 作为第二个字母“o”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26326218/
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
Select first_name where last_name has as second letter 'o'?
提问by César Villanueva
I'm using Oracle, this information comes from the "HR.EMPLOYEES" table, but I don't know how to structure that query.
我正在使用 Oracle，此信息来自“HR.EMPLOYEES”表，但我不知道如何构建该查询。
回答by Lalit Kumar B
There are several ways to achieve your goal. In no particular order:
有几种方法可以实现您的目标。没有特定的顺序：
Using SUBSTR
使用 SUBSTR
Just use SUBSTR. This function allow you to extract a part from a string:
只需使用SUBSTR. 此函数允许您从字符串中提取一部分：
SELECT first_name FROM table WHERE SUBSTR(last_name, 2, 1) = 'o'
Remember, you are only filtering the rows based on lower case as 'o' which is not same as upper case 'O'. And, you need to create a function-based indexon the column too, to avoid any performance issuesdue to FTS.
请记住，您仅将基于小写的行过滤为 'o'，这与大写的 'O' 不同。而且，您还需要function-based index在列上创建一个，以避免任何performance issues由于FTS.
If you have only one column for the name, then use SUBSTRand INSTRin the SELECTto get the first_name. I would like to leave this up to you. Let me know if you really struggle with INSTR. A hint for you to try and learn yourself, INSTR( string, substring [, start_position [, nth_appearance ] ] )
如果你有一个名字只有一列，然后使用SUBSTR和INSTR在SELECT拿到first_name。我想把这个留给你。让我知道你是否真的在挣扎INSTR。提示你尝试和学习自己，INSTR( string, substring [, start_position [, nth_appearance ] ] )
Using regular expression
使用正则表达式
The same could be achieved using REGEXP_LIKE, however, it would be much resource consuming:
使用 可以实现相同的效果REGEXP_LIKE，但是，它会消耗大量资源：
SELECT first_name FROM table WHERE REGEXP_LIKE(last_name, '^.o')
Here, I am looking for a string such as:
在这里，我正在寻找一个字符串，例如：
^after the start of the string.I have anycharacterothen an o
^在字符串开始之后.我有任何性格o然后一个o
Using LIKEpattern matching
使用LIKE模式匹配
This solution is rather database-vendor agnostic: it will work with (almost?) any RDBMS:
该解决方案与数据库供应商无关：它适用于（几乎？）任何 RDBMS：
SELECT first_name FROM table WHERE last_name LIKE '_o%'
Pattern matching is more or less like the wildcardyou might use in your shell. Except than is SQL _is used for any characterand %is used for any string(incl. empty string).
模式匹配或多或少类似于您可能在 shell 中使用的通配符。除了 SQL_用于任何字符和%用于任何字符串（包括空字符串）。
Other
其他
By searching through the web and in various Oracle's documentation you might probably be able to find several other -- more or less exotic -- options.
通过在 Web 和各种 Oracle 文档中搜索，您可能能够找到其他几个——或多或少奇特的——选项。
For example, one might think of using a virtual columnon the second letter of last_name.
例如，人们可能会想到在 的第二个字母上使用虚拟列last_name。
Or, if and only if, you need case insensitive approach, you can have a look at this demo http://lalitkumarb.wordpress.com/2014/01/22/oracle-case-insensitive-sorts-compares/. Please make sure you have utmost understanding about the trivial aspects as mentioned above, before jumping onto complex things.
或者，当且仅当您需要不区分大小写的方法时，您可以查看此演示http://lalitkumarb.wordpress.com/2014/01/22/oracle-case-insensitive-sorts-compares/。在跳到复杂的事情之前，请确保您对上面提到的琐碎方面有充分的了解。
