I use SUBSTRING_INDEX quite a lot on Eccommerce sites where for a listing of products I would like to extract a certain number of words from the actual product description:
SUBSTRING_INDEX('description', ' ' , 20) AS shortDesc
In most case this works like a charm! But there are those moments that the actual product description is devided in paragraphs. If in those cases the number of words in the first paragraph doesn't match the number of words in the SUBSTRING_INDEX, words from the second paragraph are added until it reach the number of words but with this the space between the paragraphs is included in my output. Is there any way to avoid this?
Thank you in advance
What do you want to do? Eliminate the space between paragraphs? Or show only the first paragraph?
Hi Guido, although my first reaction to your question was, lets elliminate the space between paragraphs, I think I would prefer the second option (show only the first paragraph). But if you don't mind can you show me the solution for the elimination for the space between the paragraphs as well?
Thank you in advance,
how exactly would you detect where one paragraph ends and the next one begins?
also, are you sure you should be doing this with sql in the first place?
where's your html coming from?
what about getting the whole description from the db and showing in your output, just the first paragraph? Then you could display a 'More>>' link so the user could choose to read the full description and to facilitate this, you could use a JS solution, to avid reload of the page.
@ Rudy: I have no idea either with the paragraphs, but since Guido asked explicit what I would like to do I presumed, both ways were possible! But if not what would be the best approach. The text is entered by the site owner in a customized controlroom. It is just one text area where with enter a new parapraph is created.
@IBazz I can't use your approach, the way the listing is organized, but thank you for the idea
It is just one text area where with enter a new parapraph is created.
then you can do this --
SELECT SUBSTRING_INDEX(content,CHAR(13,10),1) AS first_paragraph