NickRackham
asked on
Count length of string
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
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
ASKER
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
P.S. where do I find examples like this or reference material (very basic user here!!)
Cheers
Nick
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
minlen maxlen average
----------- ----------- -----------
6 36 16
Or, with the cast to float,
minlen maxlen average
----------- ----------- ------------
6 36 16.25
ASKER
Kelfink, that more than helps, many thanks, excellent :o) Cheers Nick
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).
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).
Excellent point, Scott. Thanks
It's been a while since I reviewed that aspect.
sincerely,
Kevin Fries
It's been a while since I reviewed that aspect.
sincerely,
Kevin Fries
ASKER
Scott, many thanks for the additional info. Nick
SELECT min(len(mycolumn)) as "minlen", max(len(mycolumn)) as "maxlen", avg(len(mycolumn)) as "average"
from myTable;