Menu
I want to find the longest VARCHAR in a specific column of a SQL Server table.
Here's an example:
ID = INT IDENTITY DESC = VARCHAR(5000) ID | Desc ---|----- 1 | a 2 | aaa 3 | aa What's the SQL to return 3? Since the longest value is 3 characters?
896
varchar [ ( n | max ) ] Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000 or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).
The size of the maximum size (m) parameter of a VARCHAR column can range from 1 to 255 bytes. If you are placing an index on a VARCHAR column, the maximum size is 254 bytes. You can store character strings that are shorter, but not longer, than the m value that you specify.
The length of a varchar column can be determined using the len() function, however this generates an error when used with the text datatype. Fortunately we can use the datalength() function to work out the length of a text field.
SQL Server databases use LEN or DATALENGTH to find field width. It also has its own function to find the maximum length of a column – COL_LENGTH. SELECT MIN(LENGTH(<column_name>)) AS MinColumnLength FROM Table; If we include any non-aggregate functions into our query then we need a GROUP BY clause.
Use the built-in functions for length and max on the description column:
SELECT MAX(LEN(DESC)) FROM table_name; Note that if your table is very large, there can be performance issues.
176
For MySQL, it's LENGTH, not LEN:
SELECT MAX(LENGTH(Desc)) FROM table_name If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
