Select something that has more/less than x character

Solution 1:

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

SELECT EmployeeName FROM EmployeeTable WHERE LEN(EmployeeName) > 4

MSDN for it states:

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

Here's the link to the 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 of 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/current/static/functions-string.html#FUNCTIONS-STRING-SQL

Solution 2:

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):

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 LENGTH function to the EmployeeName column). 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.

However, various databases offer a function indexes feature 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 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.

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.

Anyway, in short: beware of the performance characteristics of queries like the one you're trying to write.

Solution 3:

Today I was trying same in db2 and used below, in my case I had spaces at the end of varchar column data

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