Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2012
    Posts
    63

    Unanswered: select the next word

    Hello all,

    I am trying to figure if this is possible.

    I am trying to look for "keywords" in a string.

    Code:
    select charindex('$Keyword',Tbl_Obj.description) from Tbl_Obj
    This code allows me to search for $Keyword. I then want to capture the word that appears DIRECTLY after this word.

    I can use the following:
    Code:
    substring(Tbl_Obj.description, charindex('$Keyword',Tbl_Obj.description) + 9,3)
    this will capture the next 3 characters however is there anyway to capture the next word or run some sort of loop in which it stops after finding a space or CrLf?

    Thanks in advance
    penfold

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It will look ugly, but what you have to do is something like
    Code:
    substring(Tbl_Obj.description, charindex('$Keyword',Tbl_Obj.description) + 9, charindex (tbl_Obj.description, ' ', charindex('$Keyword',Tbl_Obj.description) + 10) - charindex('$Keyword',Tbl_Obj.description) + 9))
    In short, there is a third (optional) parameter for charindex, which is "Where to start looking". You just have to use that to have the inner charindex start looking for the first space after the target word, but then you need to restrict substring function from giving you more than just that one word.

  3. #3
    Join Date
    Dec 2012
    Posts
    63
    thanks for your input, this indeed works but it does lead further scenarios that cause a bit of a problem...
    I need to decide how to look for the keyword become some follow up with a space or CrLf and whatever else they wish to follow up with!

    The concept is good so I will just change it to fit to my purpose. Thanks!

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You could also try to roll your own (or google for the code for) a regex CLR function that you can use to extract just the letters that follow the keyword. Depends on how much you want to invest in this, and how well you may know a .NET language.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    "simplest" solution to the solution I can think of is to perform a Replace() on the original string before applying MCrowley's algorithm.

    That's to say that you can replace all manner of random characters such as CrLfs with spaces, to "normalise" the data for use with the search method provided.

    *shrug*
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you install FTS (Full Text Search) then this problem is easy to solve using the features that it provides.

    If I was going to implement a search like you've described without using FTS, then I'd create a Common Table Expression that searched in three stages. First I'd find rows that contained the target keyword, then I'd find the following space or CRLF, then I'd extract. Keeping the code efficient means that you have to take smaller steps, but these pay off handsomely in terms of performance when you have to process more than a million rows of data.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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