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.
For oracle/plsql you can use
Length(), mysql also uses Length.
Here is the Oracle documentation:
And here is the mySQL Documentation of
For PostgreSQL, you can use
char_length(string). Here is the PostgreSQL documentation:
Read more... Read less...
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')