SQLTeam.com Logo

Return to Using Len() in a Select Statement

Using Len() in a Select Statement

Written by Damian Maclennen on 01 August 2001

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.

Use Pubs

SELECT Title, Len(Notes) as LengthOfNotes from Titles

Will produce


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.

Datalength()

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.

Merkin