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

score 128 · Accepted Answer · answered Feb 19 '14 at 15:46

128

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.

answered Feb 19 '14 at 15:46

Gordon Linoff

1,242,037
58
646
786

Perfect!!!! it works.Thank you for prompt answer. How do i parse this String **;#WR_1;#WR_2;#WR_3;#WR_4;#** like this 'WR_1,WR_2,WR_3,WR_4 As one column data. – vuyy1182 Feb 19 '14 at 15:55
With Access, SELECT TOP returns ties. – HansUp Feb 19 '14 at 16:14
1

"Access doesn't have great functions for doing that" - Doesn't that look like a straight "Replace()" function to you? – Johnny Bones Feb 19 '14 at 17:56
@JohnnyBones . . . I was focused on the "parse" part of the statement. I think there is another question about this that you can answer. – Gordon Linoff Feb 19 '14 at 17:57
Oh. Then doesn't that look like a straight "Split()" function to you? :oP – Johnny Bones Feb 19 '14 at 17:59
I'm trying to use this user defind function in VBA 'Sub splitfn() str = ";#WR_1;#WR_2;#WR_3;#WR_4;#" var = Split(str, ";#") For i = 0 To UBound(var) Debug.Print i, var(i) Next i End Sub' In stead of hard coding the str value, how can i use parameters to get values from table from table – vuyy1182 Feb 19 '14 at 18:28

Thorsten Kettner · Answer 2 · 2016-04-21T09:51:43.060

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.

Generally good points, but if you think about it, a TOP N command doesn't require a sort of the whole table, just requires a buffer of size N. During the initial table scan it can do a binary insertion into the buffer for only those items bigger than the Nth that it's seen so far. — Tristan Reid, Nov 08 '17 at 06:34

score 13 · Answer 3 · answered Jan 25 '16 at 19:37

13

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

answered Jan 25 '16 at 19:37

tmthyjames

1,588
2
22
30

OP wanted the longest string, not the length of the longest string. – Andrew Morton Feb 19 '18 at 13:38
Annoying how this is still the top result in 2020 :/ – Lasf Sep 22 '20 at 01:58
1

`select distinct CR, length(CR) crl from some_table order by crl desc limit 1` – Neil Gaetano Lindberg Sep 14 '21 at 16:03

score 11 · Answer 4 · answered Feb 19 '14 at 15:47

11

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

answered Feb 19 '14 at 15:47

xeraphim

4,375
9
54
102

score 9 · Answer 5 · answered Oct 27 '16 at 10:07

9

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

answered Oct 27 '16 at 10:07

Srinivas Rathikrindi

556
15
24

score 6 · Answer 6 · answered Aug 29 '16 at 23:22

6

For Oracle 11g:

SELECT COL1 
FROM TABLE1 
WHERE length(COL1) = (SELECT max(length(COL1)) FROM TABLE1);

answered Aug 29 '16 at 23:22

kavehmb

9,822
1
19
22

score 5 · Answer 7 · answered Mar 23 '17 at 05:46

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;

score 2 · Answer 8 · edited Aug 21 '20 at 16:04

2

you have to do some changes by applying group by or query with in query.

"SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC LIMIT 1;"

it will return longest cityname from city.

edited Aug 21 '20 at 16:04

Dilan

2,610
7
23
33

answered Aug 21 '20 at 09:39

divyesh chavan

21
1

Revan · Answer 9 · 2020-11-02T12:33:34.907

1

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)

edited Nov 02 '20 at 12:33

answered Feb 19 '14 at 15:48

Revan

1,104
12
27

pjammer · Answer 10 · 2017-04-27T11:21:42.407

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)

score 1 · Answer 11 · edited Oct 18 '20 at 06:55

1

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)

edited Oct 18 '20 at 06:55

David Buck

3,752
35
31
35

answered Oct 18 '20 at 03:48

Sahil Sharma

11
1

score 1 · Answer 12 · answered Jan 08 '22 at 17:11

1

 SELECT w.DEPARTMENT 
 FROM  Worker w
 group by w.DEPARTMENT 
 order by length(w.DEPARTMENT) DESC 
 LIMIT 2 ;

answered Jan 08 '22 at 17:11

Hemanth Kumar

809
1
7
7

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/30770210) – zkoza Jan 08 '22 at 22:57
2

@zkoza: It _clearly_ offers an answer. It may be _wrong_. It certainly could use additional _detail_. But it nevertheless offers an _answer_. – Jeremy Caney Jan 09 '22 at 08:49
1

While this code snippet may solve the problem, it doesn't explain why or how it answers the question. Please [include an explanation for your code](//meta.stackexchange.com/q/114762/269535), as that really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Luca Kiebel Jan 09 '22 at 17:22
`LIMIT 2` gets you *the* longest string? – General Grievance Jan 10 '22 at 03:41

score 1 · Answer 13 · answered Jan 09 '22 at 12:59

In MariaDB only length and char_length worked for me.

If you use non-english letters, you better use char_length. For example:

select
 e.id,
 home.name,
 LENGTH(home.name) as length,
 CHAR_LENGTH(home.name) as char_length
from events e 
left join teams home on e.home_id  = home.id
order by CHAR_LENGTH(home.name) desc

score 0 · Answer 14 · edited Jun 03 '20 at 12:06

0

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;

edited Jun 03 '20 at 12:06

double-beep

5,031
17
33
41

answered Jun 03 '20 at 12:02

SUPS

1

score 0 · Answer 15 · answered Jul 29 '20 at 07:13

0

If column datatype is text you should use DataLength function like:

select top 1 CR, DataLength(CR)
from tbl
order by DataLength(CR) desc

answered Jul 29 '20 at 07:13

Devca

77
1
2

How to find longest string in the table column data

15 Answers15

Linked