How to select data items of a certain length?

In sql servers TSQL you could use the len function. eg

SELECT * FROM people WHERE LEN(firstname) > 10

Where people is the table name.

In mysql the function is called "Length" instead of len.


If you are bound to use a specific RDBMS then the solution is easy.

Use the LENGTH function.

Depending upon your database the length function can be LEN, Length, CarLength. Just search google for it.

According to your question

How do I select the row of a column such that the row size is <= 5 ? Is there a query for this which will work on most/all databases ?

solution can be

SELECT * FROM TableName WHERE LENGTH(name) <= 5

If you want something that can work with almost all the database and I assume that the length of your string that you want to fetch is of a significant small length. Example 5 or 8 characters then you can use something like this

 SELECT * 
 FROM tab
 WHERE
    colName LIKE ''
 OR colName LIKE '_' 
 OR colName LIKE '__'
 OR colName LIKE '___'
 OR colName LIKE '____'
 OR colName LIKE '_____'

This works with almost all major DBMS.

see example:

SQL Server

MySQL

Oracle

Postgre SQL

SQLite


Assuming you want the length in characters, the function names vary with RDBMS;

MySQL: CHAR_LENGTH().
Oracle: LENGTH().
SQL Server: LEN().
PostgreSQL: CHAR_LENGTH() or LENGTH().
SQLite: LENGTH().

If you want the length in bytes, it's instead;

MySQL: LENGTH().
Oracle: LENGTHB().
SQL Server: DATALENGTH().
PostgreSQL: OCTET_LENGTH().

For example, selecting all rows with names longer than 10 characters in MySQL would be;

SELECT * FROM myTable WHERE CHAR_LENGTH(name) > 10;

Tags:

Sql