Post reply

truncating text

  • February 15, 2010 at 2:46 pm

    #137288

    I'm currently searching a table in my database using a freetextfield like this:

    SELECT 'Page Text' AS RESULTCATEGORY,

    CONVERT(varchar(255), td.[text]) AS RESULTTEXT

    FROM (FREETEXTTABLE(textData, text, @searchstring) ftt

    INNER JOIN textData td

    ON ftt. = td.textID)

    This search works, but it returns the entire block of text as the result of the search. I was hoping to only include 50 characters before the search term and 50 characters after the search term....that way the result will show the text surrounding the search term rather than just a huge block of text.

    Is this possible with t-sql?

    Thanks!

  • February 15, 2010 at 4:52 pm

    #1119018

    You may consider using a combination of substring, left and right string functions.

    http://msdn.microsoft.com/en-us/library/ms181984.aspx

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 15, 2010 at 5:53 pm

    #1119027

    I'm not sure how you'd use substring in conjunction with something that doesn't return a starting position for the found string.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 15, 2010 at 6:08 pm

    #1119030

    Within the query as is - I can't either. However, I was thinking something more along the lines of dumping that data into a temp table and then using those functions. I'm not sure if that will work too well - was hoping it was worth a shot.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 16, 2010 at 5:09 am

    #1119176

    As Jeff says, a full-text search does not return any information concerning where the match was found. If the search term is known to be very simple (e.g. an exact word or phrase match, prefix search) you can use CHARINDEX or PATINDEX to determine a starting point. If you are doing stuff with inflectional searches, it becomes more difficult.

    I saw a technique based on a CLR routine in a full-text search book once, but I don't recall the details - sorry about that.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 16, 2010 at 7:43 am

    #1119287

    It's just a simple search, like one or 2 search terms...for example "race car", or "gasoline", "volcano", "space shuttle", etc....nothing fancy like using ANDs, ORs, etc...

    Thanks!

  • February 17, 2010 at 5:28 am

    #1119974

    Good. I hope it stays that way!

    Post back if you need further assistance with this.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply