Page 1 of 1
MySQL Select first paragraph
Posted: Wed Aug 23, 2006 10:23 am
by SteveB
I'm working with an existing database which has news posts in the following format:
Code: Select all
This is the first line.<br>
A second Line.
<br>
<br>A second paragraph.
I want to pull just the first paragraph from the database. Currently I have the following query:
Code: Select all
SUBSTRING_INDEX(news_text,'<br>', 1)
Which pulls out all the text until the first line break. How can I alter this to search for the double <br>, with the new line in between the two <br>s?
I've tried '<br>\n<br>' and so forth, but they don't seem to be recognised by MySQL.
Thanks.
Posted: Wed Aug 23, 2006 10:39 am
by CoderGoblin
Don't know the answer to your question and I know you are working in an existing database but..
In postgres when I performed something similar to this (needed to extract the first <b>text</b> tag), I included a trigger/function routine on save/update using plsql which extracted the "title" and placed it into a second column in the database.
This enabled me to perform searches on the full text or the title and rank the responses accordingly and also speeded up getting just the title at the expense of slowing down creations/edits.
In your situation I would create the trigger/function and add a "title" column to the database. Then I would
Code: Select all
UPDATE <tablename> SET <column>=<column>;
which would run the trigger an fill in the respective data.
Not sure how feasible this is in MySql.
Possible other solutions directly to your question have you also tried \n\r or combinations of the same or can you use regular expressions in MySQL ?
EDIT: Just looked up
Regular Expression in MySql 5.1. MAY be the solution if there is some way to return the part matcg and should be more robust to use with [:space:] but not exactly sure.
Posted: Wed Aug 23, 2006 10:51 am
by SteveB
An alternate to grabbing the first paragraph was to add a new field in which the user can choose what this highlighted paragraph would say (this is for a news post, with a summary on the main page linking to the full article). I decided to just take the first paragraph rather than bother with new forms and fields.
I've tried splitting the text by just '\n' and nothing happens. I can't figure out how MySQL deals with the line breaks internally.