SQL String Length – Finding Column Width
To find the string length (or width) in a field we can use the sql length function. The specific syntax to find the width can vary depending on the database. As an example Oracle databases use LENGTH() while SQL Server uses LEN(). In fact, SQL Server tends to use very specific defined functions when looking at field widths.
Most databases will use the LENGTH() function in SQL.
SELECT LENGTH(<column_name>) AS ColumnLength FROM Table;
We can see a practical example here using a SQLite database.
The resulting output gives us the length in characters across (including spaces).
As a result of spaces, we can see row 7 (Astrid) returns a length of 13 since the field includes additional spaces.
SQL Server Length Functions
In a SQL Server database, there are several functions for defining length fields. We can see in the syntax examples below how each of them are used.
SQL Server Syntax For Column Width (not including trailing spaces)
SELECT LEN(<column_name>) AS ColumnLength FROM Table;
SQL Server Syntax For Column Width (including trailing spaces)
SELECT DATALENGTH(<column_name>) AS ColumnLength FROM Table;
SQL Server Syntax For Max Column Width
SELECT COL_LENGTH(‘<table_name’,'<column_name>)’ AS MaxLength FROM Table;
Final Thoughts
Finding the string length in SQL is critical when migrating data from other sources into your database. If the lengths are too short in the target system tables it can result in data being cut off. As a result, not only will data be missing but it could potentially cause bigger problems such as data aggregation issues.
Needless to say, finding the string length using SQL will help in data modeling and data-cleansing practices. Supplementing the length function with the max function will also help in data modeling to find the maximum field length of a source database table.