Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Posts
    6

    Unanswered: Extract text from fields

    Hi if I have a table like this:

    id textfield
    1 this is fast becoming...
    2 that car went faster than ever...
    3 hey dude who's the fastest worker?
    4 the city of Belfast is located in Northern Ireland...


    how do I do the SQL so that I could have the result as:

    fast
    faster
    fastest

    I have been looking around the net but no luck yet.

    Thanks for any inputs.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The question sounds like an abstraction of your real problem, and might mean a good answer for this does not suit your requirements.

    I have not used this in SQL Server, but I have used similar in another DB product.

    1) Full Text Index the column
    2)
    Code:
    ....
    WHERE CONTAINS(myCOlumnName, ' FORMSOF (INFLECTIONAL, fast) ')
    Refs:
    Full-Text Search (SQL Server)
    CONTAINS (Transact-SQL)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE  CharIndex('Fast', textfield) > 0
    ?
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by SQL Server
    daColumn
    ----------------------------
    I'm enjoyin' me breakfast
    And why use CharIndex rather than LIKE '%fast%' anyway?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2009
    Posts
    6
    I've tried your codes guys but it returns the whole length of the contents of a field. I only want to get a certain word. Like in my sample above, the words

    fast
    faster
    fastest

    I have been trying this SUBSTRING and PATINDEX but still no success
    Last edited by berkshirea; 03-31-09 at 08:42.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can try (aircode):
    Code:
    SELECT SUBSTRING(myColumn, location, PATINDEX('%[^A-Za-z0-9]%', SUBSTRING(myColumn, location + 1, 20)) - location)
    FROM
    (
    SELECT myColumn, location = PATINDEX('%[^A-Za-z0-9]fast%', myColumn)
    ) as derT
    The problem is if Fast is the first or last word in the column - you might need to account for that too.

    You are getting into murky waters here - much more complex and you are into Natural Language Processing which requires more specialist software.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    And why use CharIndex rather than LIKE '%fast%' anyway?
    Because I could see the next part of the question coming up
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @meh table (
       some_id   int
     , some_text varchar(1000)
    )
    
    INSERT INTO @meh (some_id, some_text) VALUES (1, '1 this is fast becoming...')
    INSERT INTO @meh (some_id, some_text) VALUES (2, 'that car went faster than ever...')
    INSERT INTO @meh (some_id, some_text) VALUES (3, 'hey dude who''s the fastest worker?')
    INSERT INTO @meh (some_id, some_text) VALUES (4, 'the city of Belfast is located in Northern Ireland...')
    
    DECLARE @search_term varchar(1000)
        SET @search_term = 'fast'
    
    SELECT some_id
         , Coalesce(Left(other_text, NullIf(CharIndex(' ', other_text), 0) - 1), other_text) As result
    FROM   (
            SELECT some_id
                 , some_text
                 , Right(some_text, Len(some_text) - CharIndex('fast', some_text) + 1) As other_text
            FROM   @meh
            WHERE  some_text LIKE '%' + @search_term + '%'
           ) As a_subquery
    *shrug*
    Last edited by gvee; 03-31-09 at 09:22.
    George
    Home | Blog

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You want to program SQL to recognize the English language. A tall order. How would you want your code to handle these sentences, none of which use "fast" to refer to velocity:

    It is common for people to fast during Lent.
    The barnacle held fast to the rock.
    I like to date fast women.
    The kitten was fast asleep.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58

    Wink select case

    this could be the simplest way
    select distinct case when texfield like '%fast %' then 'Fast'
    when texfield like '%faster %' then 'Faster'
    when Texfield like '%fastest %' then 'Fastest'
    else ''end as texfield
    from testin1

    Other than that i would use charindex to get where the word fast is
    then length of the whole row value, then replace starting at the charindex

    I could provide an example of this as well, but I think if you are just looking for something quick the above statement should suffice.

  11. #11
    Join Date
    Jul 2009
    Posts
    1
    I have run into similar issues. The solution I came up with is not "efficient" from a storage point of view but works extremely well for these types of purposes. I create a function to tokenize my strings and store the individual terms in a table with a foreign key back to the original text. I then place a full text index on the tokenized table and perform my queries against the tokens not the strings. When I find what I am looking for I refer back to the source table for the full text. The benefit is that you can quickly find all of teh inflectional forms of a word that occur in your data. Unfortunately it gets a little more complicated if you want to search for multiple terms and still use full text...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •