All Things SQL

All Things Related To SQL, Databases & Cloud Based Solutions

All Things SQL 

SQL LENGTH – Finding Max & Min String Width

, , , , , ,

We can use a combination of the MAX and MIN functions in conjunction with the SQL length function to get the max or min character width of a column. As the LENGTH function is universally used in most relational databases, we can use that syntax for our examples. Finding the max or min length of a column in SQL is pretty straight-forward.

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.

Syntax for finding MAX column length for a single field

SELECT MAX(LENGTH(<column_name>)) AS MaxColumnLength FROM Table;

Syntax for finding MIN column length for a single field

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. Also, we could replace the above syntax examples with LEN or DATALENGTH for SQL Server.

RELATED: GROUP BY – Count, Sum, and More

Finding String Length for Multiple Columns

Similarly to finding a single field length we can find multiple column lengths just as easily.

Syntax for finding MAX column length for a multiple fields

SELECT MAX(LENGTH(<column_1>)) AS MaxColLen1, MAX(LENGTH(<column_2>)) AS MaxColLen2 FROM Table;

Syntax for finding MIN column length for multiple fields

SELECT MIN(LENGTH(<column_1>)) AS MinColLen1, MIN(LENGTH(<column_2>)) AS MinColLen2 FROM Table;

Practical Examples of MIN and MAX Field Length

We can see the following practical example shows us how to write the SQL to include two column values.

max string length sql multiple columns

The resulting output gives us our max string length for both columns. The longest field in our first name column is a length of 13 characters and the max length of our last name column is 12 characters.

sql max length output example

Alternatively, we could also get the minimum values with the following sql syntax.

sql min length syntax example

In this case our minimum values output for first name is 3 and last name is a length of 4.

sql min length output example
Final Thoughts

Finding the max and min lengths of a column in sql can be very useful. This is especially true when data modeling for a new database and migrating data from another system. These functions provide us valuable information for data analysis as well. Just don’t forget to include GROUP BY clause if you decide to add in non-aggregate columns into your queries.