How to select and sort records with biggest column size by characters?-CodePudding

Table Customers

ID    NAME LETTER
001   TOM  Lorem Ipsum
002   JEK  Lorem Ipsum
003   MAX  texttexttexttext...(30k letters)
004   JIZ  NULL
005   ZAK  texttexttexttext...(50k letters)

The 'Letter' data type in DB is 'text', I have some records that contain 50k letters even my microsoft sql client wont load the full size of it :/

Anyways I need to select IDS that have the biggest lenght by symbols at letter column

I tried next one: SELECT TOP 100 * FROM Customers ORDER BY CHAR_LENGTH(Letter);

but looks like my db/sql client dsnt have that function :/ also I tried len(Letter) but argument data type is invalid for len function(

CodePudding user response：

If you want the actual number of characters in the text field, cast it to a varchar, that should work for most scenarios:

select top(100) *
from Customers
order by len(cast(letter as varchar(max))) desc

If you want bytes, text can be used with datalength

select top(100) *
from Customers
order by datalength(letter) desc