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;#  

Answer 1

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.

Answer 2

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.