How to find longest string in the table column data

Question

I ve a table contains the columns like    Prefix       CR ----------------------------------------   g              WR 1  WR 2  WR 3  WR 4      v              WR 3  WR 4     j            WR 2   m            WR 1   d              WR 3  WR 4        f9           WR 3   I want to retrieve data from CR column WHERE it has the longest text string i e in current table it is   WR 1  WR 2  WR 3  WR 4    I m using   SELECT max len CR   AS Max Length String FROM table1    But it retuns   Max Length String ---------------------------------------- 26   But what i need is not the length  26   i wanted like this   Max Length String ----------------------------------------   WR 1  WR 2  WR 3  WR 4

User · Answer

You can get it like this   SELECT TOP 1 CR FROM tbl ORDER BY len CR  DESC   but i m sure  there is a more elegant way to do it

User · Answer

In MySQL you can use   SELECT CITY           LENGTH CITY  AS CHR LEN   FROM   STATION   ORDER  BY CHR LEN ASC              CITY   LIMIT  1   UNION   SELECT CITY           LENGTH CITY  AS CHR LEN   FROM   STATION   ORDER  BY CHR LEN DESC              CITY   LIMIT  1

User · Answer

This was the first result on  longest string in postgres  google search so I ll put my answer here for those looking for a postgres solution   SELECT max char length column   AS Max Length String FROM table  postgres docs  http   www postgresql org docs 9 2 static functions-string html

User · Answer

Instead of SELECT max len CR   AS Max Length String FROM table1 Use SELECT  CR  FROM table1 WHERE len CR     SELECT max len CR   FROM table1

User · Answer

The easiest way is   select top 1 CR from table t order by len CR  desc   Note that this will only return one value if there are multiple with the same longest length

User · Answer

you have to do some changes by applying group by or query with in query   quot SELECT CITY LENGTH CITY  FROM STATION ORDER BY LENGTH CITY  DESC LIMIT 1  quot   it will return longest cityname from city

User · Answer

For Postgres    SELECT column FROM table WHERE char length column     SELECT max char length column   FROM table     This will give you the string itself modified for postgres from  Thorsten Kettner answer

User · Answer

For Oracle 11g   SELECT COL1  FROM TABLE1  WHERE length COL1     SELECT max length COL1   FROM TABLE1

User · Answer

To answer your question  and get the Prefix too  for MySQL you can do   select Prefix  CR  length CR  from table1 order by length CR  DESC limit 1   and it will return    ------- ---------------------------- --------------------    Prefix  CR                                   length CR     ------- ---------------------------- --------------------    g         WR 1  WR 2  WR 3  WR 4                     26    ------- ---------------------------- --------------------  1 row in set  0 01 sec

User · Answer

You can   SELECT CR  FROM table1  WHERE len CR     SELECT max len CR   FROM table1    Having just recieved an upvote more than a year after posting this  I d like to add some information    This query gives all values with the maximum length  With a TOP 1 query you get only one of these  which is usually not desired  This query must probably read the table twice  a full table scan to get the maximum length and another full table scan to get all values of that length  These operations  however  are very simple operations and hence rather fast  With a TOP 1 query a DBMS reads all records from the table and then sorts them  So the table is read only once  but a sort operation on a whole table is quite some task and can be very slow on large tables  One would usually add DISTINCT to my query  SELECT DISTINCT CR FROM       so as to get every value just once  That would be a sort operation  but only on the few records already found  Again  no big deal  If the string lengths have to be dealt with quite often  one might think of creating a computed column  calculated field  for it  This is available as of Ms Access 2010  But reading up on this shows that you cannot index calculated fields in MS Access  As long as this holds true  there is hardly any benefit from them  Applying LEN on the strings is usually not what makes such queries slow

User · Answer

If column datatype is text you should use DataLength function like  select top 1 CR  DataLength CR  from tbl order by DataLength CR  desc

User · Answer

With two queries you can achieve this  This is for mysql    will select shortest length coulmn and display its length     only 1 row will be selected  because we limit it by 1  SELECT column  length column  FROM table order by length column  asc limit 1     will select shortest length coulmn and display its length   SELECT CITY  length city  FROM STATION order by length city  desc limit 1

User · Answer

SELECT CITY LENGTH CITY  FROM STATION GROUP BY CITY ORDER BY LENGTH CITY  ASC LIMIT 1  SELECT CITY LENGTH CITY  FROM STATION GROUP BY CITY ORDER BY LENGTH CITY  DESC LIMIT 1

[sql] How to find longest string in the table column data

Examples related to sql

Examples related to ms-access