Select something that has more less than x character

Question

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

User · Answer

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

User · Answer

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   gt  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   lt   4  or LENGTH EmployeeName   gt  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

User · Answer

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

User · Answer

If you are using SQL Server  Use the LEN  Length  function   SELECT EmployeeName FROM EmployeeTable WHERE LEN EmployeeName   gt  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

[sql] Select something that has more/less than x character

Examples related to sql