sql

How to select data items of a certain length?


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 ?

eg. id, first_name

Select only those people whose firstname is more than 10 characters. Their name is too long ?


Solution

  • 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.

    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