SQL 选择多于/少于 x 个字符的内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8509723/
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 something that has more/less than x character
提问by MNX1024
Was wondering if it's possible to select something that has more/less than x characters in SQL.
想知道是否可以在 SQL 中选择多于/少于 x 个字符的内容。
For example, I have an employee table and I want to show all employee names that has more than 4 characters in their name.
例如，我有一个员工表，我想显示姓名中包含 4 个以上字符的所有员工姓名。
Here's an example table
这是一个示例表
ID EmpName Dept
1 Johnny ACC
2 Dan IT
3 Amriel PR
4 Amy HR
回答by JonH
If you are using SQL Server, Use the LEN(Length) function:
如果您使用的是 SQL Server，请使用LEN(Length) 函数：
SELECT EmployeeName FROM EmployeeTable WHERE LEN(EmployeeName) > 4
MSDN for it states:
它的 MSDN 指出：
Returns the number of characters of the specified string expression,
excluding trailing blanks.
返回指定字符串表达式的字符数，
不包括尾随空格。
For oracle/plsql you can use Length(), mysql also uses Length.
对于 oracle/plsql，您可以使用Length()，mysql 也使用 Length。
Here is the Oracle documentation:
这是 Oracle 文档：
http://www.techonthenet.com/oracle/functions/length.php
http://www.techonthenet.com/oracle/functions/length.php
And here is the mySQL Documentation of Length(string):
这里是 mySQL 文档Length(string)：
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length
For PostgreSQL, you can use length(string)or char_length(string). Here is the PostgreSQL documentation:
对于 PostgreSQL，您可以使用length(string)或char_length(string)。这是 PostgreSQL 文档：
http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-SQL
http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-SQL
回答by Luis Casillas
JonH has covered very well the part on how to write the query. There is another significant issue that must be mentioned too, however, which is the performance characteristics of such a query. Let's repeat it here (adapted to Oracle):
JonH 已经很好地介绍了如何编写查询的部分。然而，还有另一个重要的问题必须提及，那就是此类查询的性能特征。这里再重复一遍（适配Oracle）：
SELECT EmployeeName FROM EmployeeTable WHERE LENGTH(EmployeeName) > 4;
This query is restricting the result of a function applied to a column value(the result of applying the LENGTHfunction to the EmployeeNamecolumn). In Oracle, and probably in all other RDBMSs, this means that a regular index on EmployeeName will be useless to answer this query; the database will do a full table scan, which can be really costly.
此查询限制施加到列值的函数的结果（将结果LENGTH函数到EmployeeName列）。在 Oracle 中，也可能在所有其他 RDBMS 中，这意味着 EmployeeName 上的常规索引将无法回答此查询；数据库将进行全表扫描，这可能非常昂贵。
However, various databases offer a function indexesfeature that is designed to speed up queries like this. For example, in Oracle, you can create an index like this:
然而，各种数据库提供了一个函数索引特性，旨在加速这样的查询。例如，在 Oracle 中，您可以创建这样的索引：
CREATE INDEX EmployeeTable_EmployeeName_Length ON EmployeeTable(LENGTH(EmployeeName));
This might still not help in your case, however, because the index might not be very selective for your condition. By this I mean the following: you're asking for rows where the name's length is more than 4. Let's assume that 80% of the employee names in that table are longer than 4. Well, then the database is likely going to conclude (correctly) that it's not worth using the index, because it's probably going to have to read most of the blocks in the table anyway.
但是，这对您的情况可能仍然无济于事，因为该索引可能对您的情况不是很有选择性。我的意思是：您要求名称长度超过 4 的行。假设该表中 80% 的员工姓名都长于 4。那么，数据库很可能会得出结论（正确）不值得使用索引，因为它可能无论如何都必须读取表中的大部分块。
However, if you changed the query to say LENGTH(EmployeeName) <= 4, or LENGTH(EmployeeName) > 35, assuming that very few employees have names with fewer than 5 character or more than 35, then the index would get picked and improve performance.
但是，如果您将查询更改为 say LENGTH(EmployeeName) <= 4, or LENGTH(EmployeeName) > 35，假设很少有员工的姓名少于 5 个字符或超过 35 个字符，那么索引将被选中并提高性能。
Anyway, in short: beware of the performance characteristics of queries like the one you're trying to write.
无论如何，简而言之：注意查询的性能特征，例如您尝试编写的查询。
回答by Vipin
Today I was trying same in db2 and used below, in my case I had spaces at the end of varchar column data
今天我在 db2 中尝试相同并在下面使用，在我的情况下，我在 varchar 列数据的末尾有空格
SELECT EmployeeName FROM EmployeeTable WHERE LENGTH(TRIM(EmployeeName))> 4;
SELECT EmployeeName FROM EmployeeTable WHERE LENGTH(TRIM(EmployeeName))> 4;
