SQL Server Performance Forum – Threads Archive

First 10 words in a column

I do want to get first 10 words in a column. ie using spaces..Is it possible without writing asp codes. any help is appreciated.

Its possible using a UDF, or maybe even inline using a whole series of SUBSTRING, CHARINDEX and maybe some CASE statements to avoid errors. Sorry I cant give an example. CHARINDEX takes a starting position though, so multiple CHARINDEX could be nested.
For a UDF, you could loop over CHARINDEX passing a new start position each time. I dont think either option will perform too well What is this for? I get the impression youre looking to show a summary of an article or something similar, (search engine synopsis?). If thats true, then my advice would be to simply to a LEFT(myVarCharColumn, 100) and then maybe strip the last word in your ASP code

In ASP, you can make use of split function.
sp=split(string)
Then access sp(0) to sp(9) Madhivanan Failing to plan is Planning to fail

Hm, instead of displaying the first 10 words, what about displaying the first x characters. I guess that’s what more typically is done. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

thanks for your reply. I can do in asp, instead of fetching 5000characters from database for each rows in a column and doing operation in asp, why should not we have do it in query side. my guessing is the later will be efficient?

Not necessarily. The SQL language isn’t that optimized for such string operations as client languages typically are. Btw, how long are your words, when 10 words make up for 5,000 character? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

//10 words make up for 5,000 character// no.. i said the length column might be 5000 characters (say ex). not 10 words make up for 5000 characters…
ex. we have a news article and want to show few words in home page and give links such as "more" like that

I would go with Chappy’s suggestion and do a LEFT(…, some value) and probably strip out the whole words at the client, if at all. I wouldn’t implement the selecting of the first 10 words or so in a query as it is very likely that it is faster at the client. You can’t also always rely on the fact that there is a blank after the tenth word. There might be a whole bunch of other characters there as well, such as , . : ; So you would have to code for this to catch all exceptions. I don’t think an inline query statement will be able to catch this all and a UDF will drag down performance. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

SO better do in asp, ok i will do in asp. thank you for replying

]]>