Extracting a set number of words

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

General SQL Server Forums

New to SQL Server Programming

Extracting a set number of words

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2010-10-02 : 16:02:20

Hi

I know now (thanks to this site and you fokes) how to restrict the number of characters in a cell within a select:

CAST(eventSummaryInfo AS CHAR(130))

But the problem I'm now having is that words are finishing half way through (understandably). Is there a way to restrict the characters, then ensure that the last word is complete?

Many thanks
Richard

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-02 : 17:26:03

Why don't you make the field the same size as the column?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2010-10-02 : 17:39:12

quote:
Originally posted by tkizer

Why don't you make the field the same size as the column?

Where I'm using the text is on a summary section, so it's not displaying all the text. I need to find a way of recalling roughly a set number of characters.

Any thoughts?

Richard Law

lazycoder
Starting Member

12 Posts

Posted - 2010-10-02 : 22:10:15

This returns first 130 characters from a string + characters needed to finis the last word, but no more than 200 characters.
You can put it to a scalar-valued function.

declare @minLength int, @maxLength int, @originalText varchar(8000), @i int
set @originalText = 'Original text for which you need summary.'
set @minLength = 130
set @maxLength = 200
set @i = charindex(' ', @originalText, @minLength)
select SUBSTRING(@originalText, 1, case when @i = 0 then @maxLength else @i end)

-----------------
http://it.expertmonster.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources