SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    runat="server" Golgotha's Avatar
    Join Date
    Nov 2001
    Location
    Colorado
    Posts
    2,085
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    get x # of characters in string...

    is there a way via SQL to say get me the first x number of characters from a field?

    no point in getting all 1000+ characters if I am only going to show the first 100 of them...

  2. #2
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Theres the SUBSTRING function:

    Code:
     USE pubs
     SELECT au_lname, SUBSTRING(au_fname, 1, 1)
     FROM authors
     ORDER BY au_lname

  3. #3
    runat="server" Golgotha's Avatar
    Join Date
    Nov 2001
    Location
    Colorado
    Posts
    2,085
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what's the USE part do?

    also once you do this can you still refer to the field au_fname ?

    Code:
    string strSelect = "USER_MESSAGE_LIST.ListID, USER_MESSAGE_LIST.UserID, SUBSTRING(USER_MESSAGE.MessageText,1,25), USER_MESSAGE.AlertLevel";
    
    string strFrom = "USER_MESSAGE INNER JOIN USER_MESSAGE_LIST ON USER_MESSAGE.MessageID = USER_MESSAGE_LIST.MessageID";
    
    string strWhere = "USER_MESSAGE_LIST.UserID =" + Session["UserID"]; //messages for that user only
    
    String strQuery = string.Format("SELECT {0} FROM {1} WHERE {2}", strSelect, strFrom, strWhere);
    
    
    I get this exception: A field or property with the name 'MessageText' was not found on the selected datasource.

    but, I don't have the use in there....

  4. #4
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you using SQL Server?

  5. #5
    runat="server" Golgotha's Avatar
    Join Date
    Nov 2001
    Location
    Colorado
    Posts
    2,085
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah

  6. #6
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Open up Enterprise Manager and click F1. The search for SUBSTRING



    The code I posted from straight from the SQL Server help file.

    Also, try this:

    Code:
    string strSelect = "USER_MESSAGE_LIST.ListID, USER_MESSAGE_LIST.UserID, SUBSTRING(USER_MESSAGE.MessageText,1,25) As MessageText, USER_MESSAGE.AlertLevel";
     


  7. #7
    runat="server" Golgotha's Avatar
    Join Date
    Nov 2001
    Location
    Colorado
    Posts
    2,085
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hahaha, exactly what I was just going to tell you. Thanks man

  8. #8
    runat="server" Golgotha's Avatar
    Join Date
    Nov 2001
    Location
    Colorado
    Posts
    2,085
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and now the next question is can you tell it not to break right in the middle of a word?

  9. #9
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Off Topic:

    Moved to the database forum since it's a SQL question.

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why not just use this??...
    Code:
    select LEFT(myField, 100) from myTable
    ...where "myField" is the name of the field, "100" is the length of characters you want to return (can be any number, 1, 9 , 1007, I just used 100 for your case). The LEFT starts counting from the left side of the field (or in most cases, the "beginning" of the string), likewise, you can also use RIGHT if you want to start attacking from the rear of the field.


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
  •