Sql Select Problem

[siriiven]

I am building a blog and I'm trying to have a"Read More" button to allow someone to write an article description, and place a read more link wherever they want. The read more link will have an id = systemreadmore. When the whole article gets inserted into a database it is one block of html code in a memo field.

How can I do a select statement that gets everything before the id=systemreadmore link?

Something in theory like

Select * from articles
where left(articlebody, <a href="" id=systemreadmore>Read More</a> )

Any ideas?

[Dan_Bracuk]

Check your db documentation for functions that sound like substring or position.

[craigkaminsky]

Because of the memo field you note in your summary, is it safe to assume this is an Access DB? If this is an Access DB, is there a particular benefit to running this sort of filter at the DB level?

In my previous experience, MS Access was not only a slower database (than SQL Server or MySQL, for example). but it isn't a true database server and easily locks up when experiencing higher loads.

It might be easier, and without any significant a performance, to manipulate the article body content/string in CF.

<cfquery name="rs" datasource="#dsn#>
select article_id, article_body
from articles
where article_id = #id#
</cfquery>

Once you get the record you want, you can use a variety of ColdFusion's string functions (see live docs for more options) to pull the section you want from the article_body.

<cfscript>
// quick and dirty -- certainly more effective ways
readMorePos = ReFindNoCase("id=systemreadmore",rs.article_body);
readMoreBody = RemoveChars(rs.article_body,1,readMorePos-1);
</cfscript>

<cfoutput>
#readMoreBody#
</cfoutput>

[siriiven]

Thanks a lot guys for the ideas. I will have to look into these and see what I can come up with. I don't really need to do this processing on the database side, I can do it through CF logic also.