Menu
Is there a simple sql query that can help me to determine the average number of characters that a (text) database field has?
For instance my field is called "message". Ideally I would love to do something like this...
select average(characterlength(message)) from mydatabasetable
is this even possible through sql?
Thanks!
843
The avg() function has the following syntax: SELECT AVG( column_name ) FROM table_name; The avg() function can be used with the SELECT query for retrieving data from a table.
LEN() function calculates the number of characters of an input string, excluding the trailing spaces. It is an expression that can be a constant, variable, or column of either character or binary data. Returns : It returns the number of characters of an input string, excluding the trailing spaces.
Partly, a column is defined by where it appears, but it shares some common characteristics: Typically, it is short, between 750 and 800 words.
In SQL, sometimes we need to find the average value of a column based on another column of the table such as finding the subject-wise average marks scored by the students of a class. This involves the use of the GROUP BY clause along with the AGGREGATE function like AVG.
Edit
Original bad phrasing: In SQL Server, LEN is for varchar fields. For Text fields, try DATALENGTH
Correction because @gbn is right: LEN will not work with Text or NText datatypes. For TEXT, try Datalength.
End Edit
SELECT AVG(DATALENGTH(yourtextfield)) AS TEXTFieldSize
Edit - added
The above is for the TEXT datatype. For NTEXT, divide by 2.
93
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
