Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Getting LEN of field type ntext

Posted on 2002-07-29
5
1,653 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 50 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

840 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