?
Solved

Getting LEN of field type ntext

Posted on 2002-07-29
5
Medium Priority
?
1,683 Views
Last Modified: 2012-06-22
Hi,

SELECT propertyID FROM properties WHERE LEN(propertyDescription) > 200

gives me the result

Server: Msg 8116, Level 16, State 2, Line 1
Argument data type ntext is invalid for argument 1 of len function.

So maybe I can't len ntext fields?

Any one know of a work around?

Ben
0
Comment
Question by:bendecko
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7187237
use datalength() function:

SELECT propertyID
FROM properties
WHERE DATALENGTH(propertyDescription) > 200

CHeers
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 200 total points
ID: 7187244
BTW, ntext is Unicode text, so it takes 2 bytes to store one character.
So, if DATALENGHT(propertyDescription) = 200, actual text lenght would be only 100.

Your query should look like this:

SELECT propertyID
FROM properties
WHERE DATALENGTH(propertyDescription) > 400
0
 
LVL 1

Author Comment

by:bendecko
ID: 7187279
Great!

angelIII I think I'm going to split the point on this one between you two as if Rimvis hadn't suggested his comment I'd have been sratching my head for ages!!!!

No problem I hope?

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7187288
Give all the points to Rimvis, his answer was complete!
CHeers
0
 
LVL 1

Author Comment

by:bendecko
ID: 7187387
That very noble of you angelIII I'll sort you out next time.

Ben
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question