We help IT Professionals succeed at work.

Count length of string

NickRackham
NickRackham asked
on
Hi All,

I'd like to know what the SQL statement would be to count the length of a string!! i.e. I have a table in an SQL database and I want to count the longest strig length, the shortest string length and the average string length for that column.

Can someone tell me what the SQL would be for something like count max string length, And Count Shortest String Length and Average String Length for Table Name, Column Name

I'd like a quick response hence points (unless it really is that complicated in which case I'll double it.

Many thanks
Comment
Watch Question

Commented:
Easy...

SELECT min(len(mycolumn)) as "minlen", max(len(mycolumn)) as "maxlen", avg(len(mycolumn)) as "average"
from myTable;

Author

Commented:
kelfink, does this analyse the length of every string to get the average? Cheers Nick

P.S. where do I find examples like this or reference material (very basic user here!!)

Cheers

Nick
Commented:
well, books online (came with Sql Server) certainly has this info.  Any other basic SQL reference will tell you about MIN, MAX and AVG.  

Ok, when you say "analyse the length of every string to get the average"  it makes me nervous.

The whole point of finding the minimum/maximum/average of a set of data is to analyse the entire set.  So yes, all of the records in the table will be analysed.   If you have 10 rows, and the column "mycolumn" has data of varying length in each, then the query will give you an analysis based on every row you selected...

Let's try an example:
drop table student;
create table student ( name varchar(40) );
insert into student values ( 'John Smith');
insert into student values ( 'George W Bush');
insert into student values ( 'Prince');
insert into student values ( 'Someone with a very long name indeed');

SELECT min(len(name)) as "minlen", max(len(name)) as "maxlen", avg(len(name)) as "average"
from student;

On some databases, to get a more precise answer, you might need to "cast" the results of the "len" function, so get a floating point answer...

SELECT min(len(name)) as "minlen", max(len(name)) as "maxlen", avg(cast (len(name) as float)) as "average"
from student;

But that's a little more advanced.


Does this help?

Commented:
Oh.  forgot to put in the results of the samle queries...
minlen      maxlen      average    
----------- ----------- -----------
6           36          16

Or, with the cast to float,
minlen      maxlen      average    
----------- ----------- ------------
6           36          16.25

Author

Commented:
Kelfink, that more than helps, many thanks, excellent :o) Cheers Nick
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
One minor note that may or may not concern you.

If the column has any NULL values, the AVG function will ignore them.  So, if there are 110 rows, 10 of which are NULL, and the total string length is 800, then the avg len is 8.00 (800/(110 - 10)).

If you want the avg for every row, regardless of NULLs or not (that is, you want to treat NULL as if it were a string length of 0), you need to add a calculation to the select:
SELECT ..., SUM(LEN(column))/COUNT(*)
For the above example, this would be 7.27 (800/110).

Commented:
Excellent point, Scott.  Thanks

It's been a while since I reviewed that aspect.

sincerely,
Kevin Fries

Author

Commented:
Scott, many thanks for the additional info. Nick

Explore More ContentExplore courses, solutions, and other research materials related to this topic.