how to grab first 20 words from the database

[flyclassic22]

I've a database table with columns Description.
However i would only like to grab the first 20 words from the field, how do i go about doing it in ASP.NET with MSSQL ?

Any idea, algorithmn to share?

 

[AlexCuse]

It would be much easier if you set an approximate number of characters, and looked for the nearest space starting from there.

I have been working on something to return a specific number of words this morning but having no luck so far.

How big is yoru table? If it is not very big and speed is not a concern, I have a looping function somewhere that will grab them for you.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ca8msm]

Alternatively, and depending on what you are actually using the data for, you could do it in the application itself.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[nice95gle]

This will get you started on getting the first 20 words of a string

declare @t varchar(400)
declare @i int
declare @w int
Declare @e varchar(8000)

set @t='one two three four five six seven eight nine ten eleven twelve thirteen fourteen fifteen sixteen seventeen eighteen nineteen twenty twenty-one twenty-two twenty-three etc...'
set @e=''
set @i=1
while @i < 21
begin
	set @w =PATINDEX('% %',@t)

	set @e = @e +' '+ SUBSTRING(@t,1,@w)

	set @t = ltrim(stuff(@t,1,@w,''))
set @i=@i+1

end
print ltrim(rtrim(@e))

Well Done is better than well said
- Ben Franklin

 

[AlexCuse]

Just what I had in mind. Imagine running that on a million+ row table though :-(


Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

nice95gle

Try your code with the following values...

set @t='one two three '
set @t='one two three'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

There is no efficient way to do this. As you can see from the proferred solutions you will have to loop through each character until you find 20 spaces or run out of spaces before you get to 20! Not exactly a good thing to do. I would personally tell them that this is a bad idea and be done with it.

Further if you do only need a small subset of this field for searching on I would put that data in a separate field at the time the record is added. This will still be inefficient, but much more efficient than searching on it multiple times a day because you only have to do it once for each record (or update). If fact if you keep this ridiculous requirement and expect to have a large recordset, it will be the only practical way to do it as this would take forever on a million plus record set as Alex pointed out.

Questions about posting. See faq183-874

 

[AlexCuse]

nice95gle - this might help ;-)

len(@t) - len(replace(@t, ' ', ''))

Ignorance of certain subjects is a great part of wisdom

 

[zeeshanmbutt]

Cant you use the left function get the first 20 characters.

--Step 1
create table
#mytable
(Name varchar (100))

--Step 2
insert #mytable
select 'John'
union all select 'Anthony Queen 123456 789 111 76 583 2892 7624523 494'
union all select 'Nicole Kidman 782 74d jdiefvnmf dghdhd q-3456353474 '
union all select 'Paula Abdul dhdi 7nf kqwi 485nc0v 90f90 90f ggg'

--Step 3
Select name,left(name,20) First20Character from #mytable

 

[zeeshanmbutt]

wilsonfuqi,

Sorry I did not notice that you are looking for words not characters.

 

[nice95gle]

quick fix

Remember this is just to get him started

declare @t varchar(400)
declare @i int
declare @w int
Declare @e varchar(8000)

set @t='one two three'+ REPLICATE(' ',1)

set @e=''
set @i=1
while @i < 21
begin
	set @w =PATINDEX('% %',@t)
		
	set @e = @e +' '+ SUBSTRING(@t,1,@w)

	set @t = ltrim(stuff(@t,1,@w,''))
set @i=@i+1

If @w = 0 	
		BREAK
		     ELSE
		CONTINUE
end

print ltrim(rtrim(@e))

Well Done is better than well said
- Ben Franklin

 

[gmmastros]

nice95gle,

Please don't think I was criticizing your code. That was not my intention.

Because the original poster wants words... this gets very messy. (by messy I mean, hard to maintain). For example, what happens when there are 2 spaces between words. Or what about hyphenated words? Do they count as 2 words or 1?

My intention was to point out that this is NOT something to be taken lightly and is probably better off done in the front end (or not at all).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nice95gle]

gmmastros

No problem...I fully understand.
My code gets criticized all day long (by me). I like to give people options and let them decide from there what they would like to do. I'm hoping as he starts to implement this he will see that this is not the best way to do it. If anything it will take a lonngggg time with a big table. But if I give him the anwser he will not learn anything. That's why I usally point people to an article or whitepaper.


Oh and the PATINDEX and the LTRIM will take care of multiple spaces

Well Done is better than well said
- Ben Franklin

 

[flyclassic22]

Thank you everybody!!.. erm.. sorry i'm abit of an idiot here, i don't understand the syntax by the answers here.. I'm using Visual Webdeveloper with C#..., i'm quite an programmer idiot, can anyone enlighten me..? are you all writing in VB?

 

[ca8msm]

The code people have posted is for SQL Server since you asked the question in the SQL Server forum. If you want an ASP.NET example, I suggest you ask in that forum.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[flyclassic22]

OPPS, wrong POST!..

 

[onpnt]

Even knowing you are working on this in ASP.NET the concept of keeping this on the database level is a good one. Without question it would be the point in my tier that I would do this work. There is no real reason your application should use the memory to bring not needed data in.

I would suggest keeping with the direction that has been provided while integrating it into your .NET process

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}