This is small sample of my salesDetail table and its [Kind of business] column:

kind of business
-------------------------------------
Retail and food services sales, total ………………………………………...…………………………………………………………………………………………………

Total (excl. motor vehicle and parts dealers) ………………………………………...…………………………………………………………………………………………………

I want to separate all data by white space and then calculate maximum length of separated string. I tried this code

SELECT MAX(LEN(value))
FROM salesDetail
CROSS APPLY STRING_SPLIT([kind of business], ' ')
GROUP BY value

This returns the length of individual string not maximum of all string.

The results are shown in the screenshot below.

So then I tried this code:

SELECT MAX(COUNT(value))
FROM salesDetail
CROSS APPLY STRING_SPLIT([kind of business], ' ')
GROUP BY value

This one throws an error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Expected output:

147    (this is maximum number in this column)

I want maximum value from the result as shown in picture

How should I fix this?

CodePudding user response：

Simply remove the final GROUP BY

select max(len(value)) max_len
from salesDetail 
CROSS APPLY STRING_SPLIT([kind of business], ' ')