Choose one that has more / less than x

Question

I wonder if it's possible to select something that has more / less than x characters in SQL.

For example, I have a table of employees, and I want to show the names of all employees who have more than 4 characters.

Here is an example table

ID EmpName Dept 1 Johnny ACC 2 Dan IT 3 Amriel PR 4 Amy HR 

Answer 1

If you are using SQL Server, use the LEN function (Length):

SELECT EmployeeName FROM EmployeeTable WHERE LEN(EmployeeName) > 4

MSDN for him states:

Returns the number of characters of the specified string expression,
excluding trailing spaces.

Here is a link to MSDN

For oracle / plsql you can use Length() , mysql also uses Length.

Here is the Oracle documentation:

http://www.techonthenet.com/oracle/functions/length.php

And here is the mySQL documentation for Length(string) :

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:

http://www.postgresql.org/docs/9.0/interactive/functions-string.html#FUNCTIONS-STRING-SQL

Answer 2

JohnH spoke very well about how to write a query. However, there is another important issue that should be mentioned, namely the performance characteristics of such a request. Let me repeat this here (adapted to Oracle):

 SELECT EmployeeName FROM EmployeeTable WHERE LENGTH(EmployeeName) > 4; 

This query restricts the result of the function applied to the column value (the result of applying the LENGTH function to the EmployeeName column). In Oracle, and probably in all other RDBMSs, this means that the regular EmployeeName index will be useless to answer this query; the database will perform a full table scan, which can be very expensive.

However, various databases offer an index function that is designed to speed up queries like this. For example, in Oracle, you can create an index like this:

 CREATE INDEX EmployeeTable_EmployeeName_Length ON EmployeeTable(LENGTH(EmployeeName)); 

This may still not help in your case, because the index may not be very selective for your condition. By this, I mean the following: you request rows where the name is longer than 4. Suppose that 80% of the names of employees in this table are longer than 4. Well, then the database is probably going to conclude (correctly) that it is not worth using an index, because he probably will have to read most of the blocks in the table.

However, if you changed the query to say LENGTH(EmployeeName) <= 4 or LENGTH(EmployeeName) > 35 , considering that very few employees have names with less than 5 characters or more than 35, then the index will be selected and improved.

Anyway, in short: beware of query performance characteristics like the ones you are trying to write.