Silly sql question and brain freezes

[college_amy]

Ok.. this is a silly / easy question that I know the answer too - but for some reason, I can't get the answer to come to me at this moment...

 

I have a string (a long string) and I want to cut off all the remaining text after the 6th or 7th word (looking for the first white space area after that word) and put "..." in the remainder of the string's place. (You know, kinda like a preview of a news article or something)

 

How can I do this in sql?

 

I have done it before, but can't remember or think of it for the life of me!

 

Any help is appreciated... :)

[PlausiblyDamp]

Doesn't SQL provide a LEFT function that does this?

[Nerseus]

I'm not sure how to do what you want in SQL - does it have to be in SQL? The .net framework provides a method to trim a string with the "..." automatically. You most often see it in a grid, or a tooltip for a long filename and path.

 

I'd fear that any pure SQL solution is going to be pretty slow. If you're not too worried about performance, I'd guess you'd have to come up with some kind of formula. First pass guess is something like:

SUBSTRING(@s, 1, <formula>) + '...'

 

where the "<formula>" is your own choosing. Suppose the longest string you want is 30 characters. Find the first whitespace before 30 and use that for the substring length above. If there is no whitespace before 30, then set your substring length to 30. This is most easily implemented with a CASE statement, but that gets pretty slow pretty quick

 

-ner

[college_amy]

No, it doesn't have to be done in SQL - I thought it might be faster that way just to do it in the stored procedure (I did it like that before, just can't remember how). Do you think that it really takes a big performance hit by doing it that way? I don't want to do that if it does...

 

Next question would be.. how would I do it in .net?

 

 

PlausiblyDamp -

The LEFT function in SQL that I think you are referring to is the Ltrim and it removes all white spaces to the left of the specified character...

Is that the function you were referring to? Or is there another one I just don't know about (which could be highly likely since I have no memory function at all anymore, makes codin' a beotch sometimes) OH and add in the fact I am working with VS2005 and SQLSrvr2005, both of which are night and day to their predecessors..

 

Anywho - thanks for the info.. and I appreciate any addtl help you may provide.. :)