Memo field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Memo field

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-05-13 : 04:01:23

Hi
I want to run a query to select the first 100 characters of a note (memo) field.

But if I try using the LEFT function as below;

select accountno, left(notes,100), from table

I get the following error

Argument data type text is invalid for argument 1 of left function.

is there a way to do this?

thanks

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-13 : 04:07:10

you can use substring function. this extracts the fixed number of characters in a string.

select substr(notes,1,100)

mrm23
Posting Yak Master

198 Posts

Posted - 2010-05-13 : 04:12:13

sorry it must be select SUBSTRING(notes,1,100)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 04:16:07

quote:
Originally posted by icw

Hi
I want to run a query to select the first 100 characters of a note (memo) field.

But if I try using the LEFT function as below;

select accountno, left(notes,100), from table

I get the following error

Argument data type text is invalid for argument 1 of left function.

is there a way to do this?

thanks

It is because left and right functions wont work on text datatype
You need to use substring as suggested
But I would say you should consider using varchar(max) datatype as text and ntext wont be supported in future release of SQL Server

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-05-13 : 04:24:55

plus (as I'm sure Madhi was implying, but just for clarity) VARCHAR(MAX) supports all the normal string functions, including LEFT, whereas the deprecated TEXT only supports a few of them.

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-05-13 : 04:28:05

yet again you guys save my life

thanks a lot

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 05:25:44

quote:
Originally posted by Kristen

plus (as I'm sure Madhi was implying, but just for clarity) VARCHAR(MAX) supports all the normal string functions, including LEFT, whereas the deprecated TEXT only supports a few of them.

You are correct. I corrected my previous reply
Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources