Select something that has more/less than x character

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

For example, I have an employee table and I want to show all employee names that has more than 4 characters in their name.

Here's an example table

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

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

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.

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;

If your experiencing the same problem while querying a DB2 database, you'll need to use the below query.

SELECT * 
FROM OPENQUERY(LINK_DB,'SELECT
CITY,
cast(STATE as varchar(40)) 
FROM DATABASE')