SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Substring_index

  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Substring_index

    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:
    Code MySQL:
    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
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What do you want to do? Eliminate the space between paragraphs? Or show only the first paragraph?

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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,
    donboe
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    ?

    bazz

  6. #6
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @ 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
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by donboe View Post
    It is just one text area where with enter a new parapraph is created.
    then you can do this --
    Code:
    SELECT SUBSTRING_INDEX(content,CHAR(13,10),1) AS first_paragraph
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •