Using Len() in a Select Statement
By Damian Maclennen
on 1 August 2001
| 15 Comments
| Tags: Queries
Maya writes "My select statement returns a resultset with one field, 'ResultText'. I would like to get a length of that field. Is there a way in SQL to do it like in Visual Basic ?"
Yes Maya, there are two ways of doing this.
The first is the Len() function which works just like Visual Basic's Len() function.
SELECT Title, Len(Notes) as LengthOfNotes from Titles
The Busy Executive's Database Guide 101
Cooking with Computers: Surreptitious Balance Sheets 76
You Can Combat Computer Stress! 119
Straight Talk About Computers 91
Silicon Valley Gastronomic Treats 52
The Gourmet Microwave 72
The Psychology of Computer Cooking NULL
As you can see, the Len of a NULL value is NULL.
If you try to use len() on a Text field, you will get an error
Argument data type text is invalid for argument 1 of len function.
The answer to this is the Datalength() function which will return the length of any expression. This can be used on all data types including text, ntext, image and varbinary.
Hope that clears it all up.