Show first * characters of a field

[Daz]

I want to add articles to a database with the intention of displaying the first so many characters (like an intro) of the field on the home page. The database would be an Access db with the field holding the html for the page in question. How would I go about this?

[vgarcia]

SELECT left(myColumn, 50) AS shortDesc FROM table;

This would bring in the first 50 characters of that column. This works in SQL server; I'm only running on the assumption that it works in Access, but try it out.

[Thing]

I like doing it like this:

'same query as vgarcia wrote
set rs = conn.execute("SELECT left(myColumn, 50) AS shortDesc FROM table")
 
'display the desc
response.write rs("shortdesc") & "..."

I just add three periods to let people know it is actually longer than what is being displayed.

[DaveMaxwell]

You might want to consider doing it outside of SQL just for the simple fact the 50th position might be in the middle of a word, and I'd think you'd want to show the whole word.

Something like this should work OK (warning not tested for ffs(fat finger syndrome)):

Function Get50(FieldIn)
 If mid(FieldIn, 50, 1) = " " then
  Get50 = Left(FieldIn, 50)
 ElseIf 
  HoldLength = instr(FieldIn, ",", 51)
  If HoldLength = 0 then HoldLength = instrev(FieldIn, ",", 50) ' in case no spaces are after 50
  if HoldLength = 0 then
   ' no spaces found at all (doubtful but could happen), so take first 50 characters
   Get50 = Left(FieldIn, ",", 50)
  else
   ' take length based on the appropriate space.
   Get50 = Left(FieldIn, HoldLength)
  end if
End Function

[Thing]

' no spaces found at all (doubtful but could happen), so take first 50 characters

Ok so I had to do a google search on a word that would fit this "what if" statement, and here it is:

aequeosalinocalcalinoceraceoaluminosocupreovitriolic (52 characters)

So if your ever referencing aequeosalinocalcalinoceraceoaluminosocupreovitriolic then Dave's function will work!

[DaveMaxwell]

Ok so I had to do a google search on a word that would fit this "what if" statement, and here it is:

aequeosalinocalcalinoceraceoaluminosocupreovitriolic (52 characters)

So if your ever referencing aequeosalinocalcalinoceraceoaluminosocupreovitriolic then Dave's function will work!

Hey, gotta cover the bases

[Daz]

With the SQL statement below, I'm not connecting to the table at all. If I take out everything from left SELECT to FROM and replace with * it works fine. Are you sure the syntax is correct?

strSQL = "SELECT left(Text, 50) AS shortDesc FROM content"

DaveMaxwell, with your solution I kept getting errors with the syntax also and should "FieldIn" be replaced with the column/field I want to display?

[Thing]

To use the function call it like this:

response.write Get50(yourvariable)

You don't have to change any of his code in the function for it to work.

As far as the LEFT working in your query, it looks just fine to me. What error are you getting??

[DaveMaxwell]

DaveMaxwell, with your solution I kept getting errors with the syntax also and should "FieldIn" be replaced with the column/field I want to display?

What errors are you getting? That would help to figure it out.

Like williamsba said, you need to call the function passing the field you want to get the first 50 characters from.

[DaveMaxwell]

Actually, there were a couple errors in that. I've taken the time to rewrite the function a bit to make it more robust and flexible so I could post it to the asp funky functions thread. This code does work (I tested it...)

Usage: Response.Write GetFirstN(strVariable, 50)

Function GetFirstN(FieldIn, NumChars)
 Dim HoldLength
 ' Eliminate excessive spaces from end
 FieldIn = Trim(FieldIn)
 ' If Less than the maximum, just return the field
 If Len(FieldIn) <= NumChars then
  GetFirstN = FieldIn : Exit Function
 End If
 
 If mid(FieldIn, NumChars, 1) = " " then
  GetFirstN = Left(FieldIn, NumChars)
 Else 
	HoldLength = instr(NumChars + 1, FieldIn, " ")
  If HoldLength = 0 then HoldLength = instrev(FieldIn, " ", NumChars) ' in case no spaces are after 
  If HoldLength = 0 then
	  ' no spaces found at all (doubtful but could happen), so take first numchars characters
   GetFirstN = Left(FieldIn, ",", NumChars)
  else
	  ' take length based on the appropriate space.
   GetFirstN = Left(FieldIn, HoldLength)
	End if
 End If
End Function

[r937]

dave, what kind of function is that?

i don't do asp, but the "response.write" suggests
that it is a function that you would run after
retrieving the entire field from the database

that solution could have very poor performance

if the field in question is indeed an article,
then let's say it is average 10K

if you have a 50-char "teaser" for a dozen links on the
home page of a site, with "more" or "read entire article" links,
then you are suggesting to retrieve a dozen 10K articles
just so that you can pull out a 50-char teaser from each one?

far better to use the built in Access string handling functions
to split the teaser on a space before returning a dozen
~50-char query results

[Daz]

I was getting an error saying item cannot be found in recordset. I've come up with my own solution anyway which seems to do the trick

strText = Left(rs("Text"),200)

[DaveMaxwell]

dave, what kind of function is that?

i don't do asp, but the "response.write" suggests
that it is a function that you would run after
retrieving the entire field from the database

that solution could have very poor performance

if the field in question is indeed an article,
then let's say it is average 10K

if you have a 50-char "teaser" for a dozen links on the
home page of a site, with "more" or "read entire article" links,
then you are suggesting to retrieve a dozen 10K articles
just so that you can pull out a 50-char teaser from each one?

far better to use the built in Access string handling functions
to split the teaser on a space before returning a dozen
~50-char query results

You're right that it would perform badly in that case (extremely large text
articles). The best approach would be a combination of the two. If you
wanted to have the first 50 characters of the article as a teaser, then you
retrieve the first 100 characters of the article and then parse the text from
there. Would give you the boost of retrieving limited amounts of data from
the database, but still give you the "clean" look of ending the shortened text
on a whole word and not in the middle of a word. That personally would drive
me nuts.

[Thing]

I got one for ya Dave. I do something similar to this, however I run their input through a function that looks for links and then places the appropriate <a href> code around the link before inserting into DB. The problem is when I cut my data if its longer than 300 characters, and it's in the middle of a link it really messes things up.

Do you happen to have anything that could handle that?

Here is the function:

Function LinkURLs(tempTxt)
   Dim regEx
   Set regEx = New RegExp
   regEx.Global = True
   regEx.IgnoreCase = True
   temptxt = replace(temptxt, "'", "''")
   'Hyperlink Email Addresses
   regEx.Pattern = "([_.a-z0-9-]+@[_.a-z0-9-]+\.[a-z]{2,3})"
   tempTxt = regEx.Replace(tempTxt, "<a href=""mailto:$1"">$1</a>")
   'Hyperlink URL's
   regEx.Pattern = "((www\.|(http|https|ftp|news|file)+\:\/\/)[_.a-z0-9-]+\.[a-z0-9\/_:@=.+?,##%&~-]*[^.|\'|\# |!|\(|?|,| |>|<|;|\)])"
   tempTxt = regEx.Replace(tempTxt, "<a href=""$1"" target=""_blank"">$1</a>")
   'Make <a href="www = <a href="http://www
   tempTxt = Replace(tempTxt, "href=""www", "href=""http://www")
   LinkURLs = tempTxt
End Function

[asp_funda]

aequeosalinocalcalinoceraceoaluminosocupreovitriolic (52 characters)

So if your ever referencing aequeosalinocalcalinoceraceoaluminosocupreovitriolic then Dave's function will work!

I don't think its any word, is it? My dictionary went all crazy & crashed when I tried to look up the meaning of it.
Also, www.dictionary.com says that they don't have any entry for that word at http://dictionary.reference.com/sear...osocupreovitri.

[Thing]

http://www.google.com/search?sourcei...upreovitriolic

[algio]

Actually, there were a couple errors in that. I've taken the time to rewrite the function a bit to make it more robust and flexible so I could post it to the asp funky functions thread. This code does work (I tested it...)

Usage: Response.Write GetFirstN(strVariable, 50)

Function GetFirstN(FieldIn, NumChars)
 Dim HoldLength
 ' Eliminate excessive spaces from end
 FieldIn = Trim(FieldIn)
 ' If Less than the maximum, just return the field
 If Len(FieldIn) <= NumChars then
  GetFirstN = FieldIn : Exit Function
 End If
 
 If mid(FieldIn, NumChars, 1) = " " then
  GetFirstN = Left(FieldIn, NumChars)
 Else 
	HoldLength = instr(NumChars + 1, FieldIn, " ")
  If HoldLength = 0 then HoldLength = instrev(FieldIn, " ", NumChars) ' in case no spaces are after 
  If HoldLength = 0 then
	  ' no spaces found at all (doubtful but could happen), so take first numchars characters
   GetFirstN = Left(FieldIn, ",", NumChars)
  else
	  ' take length based on the appropriate space.
   GetFirstN = Left(FieldIn, HoldLength)
	End if
 End If
End Function

Hi guys, sorry to bring this up. I found very useful this function but sometimes gettin following error:

Microsoft VBScript runtime (0x800A005E)
Invalid use of Null: 'HoldLength'

Can not understand it.
Please help
Thank you in advance