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.
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.
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.
Alternatively, we could also get the minimum values with the following sql syntax.
In this case our minimum values output for first name is 3 and last name is a length of 4.
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.