Hi
I have trouble with limiting selected characters from database. I instert some html code (article) to database, then i want to show first x characters on a website...the problem is, that when I use SUBSTRING to limit character count, often happens that my selected string ends in the middle of html tag, for example I have in table:
"
<h3><span class="mw-headline">Lasers</span></h3>
<p><a title="Laser" href="http://en.wikipedia.org/wiki/Laser">Lasers</a> emitting in the yellow part of the spectrum are much less common than most other colors.
"

and it may happen that I select
"<h3><span class="mw-headline">Lasers</span></h3>
<p><a title="Laser" href="http://en.wikipedia.o
"

so I can not echo that on a website, because substring cuts it in the opened html tag. is there any way how to select something after for example tag closure? or before beginning one.

thanks for any help

Not sure what language you're using, but in ASP C# (which I assume it is, if you're using substring) it would be something like:


If you're using another language I can't help much, but that'll do it for C#

SUBSTRING is in almost every web-language...im using PHP i was talking about sql command for selecting
$sql = "SELECT SUBSTRING(sprava,1,500) AS sprava from ....;

Oh, the substring is in the SQL query? Sorry, thought you were parsing it after the query

While I understand why your doing it there, speed, I do think your stuck. Your going to have to do post processing on the query, making this 'optimized' SQL redundant. I would recommend that you make a separate column in your SQL database that is exclusivity for a preview. This column would hold the preview without any HTML in it, and you should insert or update this each time the a article is added or edited.

Use regular expressions to strip your string from html tags and then send the desired substring as output.

I think he wants to keep the HTML
I came up with this fairly exotic query:
Selects everything up to the last closed HTML tag after 50 chars, don't know how SUBSTRING reacts if LOCATE returns 0 though.

When applied to your example text

Quote :

<h3><span class="mw-headline">Lasers</span></h3>
<p><a title="Laser" href="http://en.wikipedia.org/wiki/Laser">Lasers</a> emitting in the yellow part of the spectrum are much less common than most other colors.

it returned

Quote :

<h3><span class="mw-headline">Lasers</span></h3>
<p><a title="Laser" href="http://en.wikipedia.org/wiki/Laser">Lasers</a>

Hope that helps

It does of course mean images will have to be marked up with a closing tag, but IIRC that's still perfectly valid

Quote from boothy :

It does of course mean images will have to be marked up with a closing tag, but IIRC that's still perfectly valid

True, can't come up with a work-around though

Quote from morpha :

I think he wants to keep the HTML

Your idea is OK as long as there are no nested tags. The above result has an open paragraph tag for example.

However, combining this with my previous suggestion he gets the best of both - less data going through the wire from his db server to his web server and no problems with html tags.

Yeah, I'm trying something else now, do the first part with LOCATE (last opening '<'), then find closing tags in a substring from the located position to the end of the string with regexing for the closing tag, that way it'll be xhtml compatible. Still no SQL-only solution for nested tags though.
€: Nope, that's a no-go, MySQL's regex implementation is only for matching