SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Wizard
    Join Date
    Aug 2002
    Location
    N.Ireland
    Posts
    1,046
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Show first * characters of a field

    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?

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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.

  3. #3
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I like doing it like this:
    Code:
    '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.


  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    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)):

    Code:
    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
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    ' 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!


  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by williamsba
    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
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    SitePoint Wizard
    Join Date
    Aug 2002
    Location
    N.Ireland
    Posts
    1,046
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Code:
    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?

  8. #8
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    To use the function call it like this:

    Code:
    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??

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Daz
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  10. #10
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    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)

    Code:
    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
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

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

  12. #12
    SitePoint Wizard
    Join Date
    Aug 2002
    Location
    N.Ireland
    Posts
    1,046
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

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

  13. #13
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by 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
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  14. #14
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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

  15. #15
    SitePoint Wizard silver trophybronze trophy asp_funda's Avatar
    Join Date
    Jun 2003
    Location
    ether
    Posts
    4,497
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by williamsba
    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.
    Our lives teach us who we are.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Me - Photo Blog - Personal Blog - Dev Blog
    iG:Syntax Hiliter -- Colourize your code in WordPress!!

  16. #16
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

  17. #17
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    Athens
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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)

    Code:
    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


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
  •