"Match whole words only" vs. "Exact Match"

mjdiemmjdiem Posts: 1 New member
We upgraded to SQL Search v3.1.6, which appears to have removed the "Exact Match" option and now has "Match whole words only".  The "Match whole words only" appears to be pretty useless is practice.  For example if a procedure has the text "SELECT [Column 1], [Column 2] FROM dbo.Table", then searching for "[Column 1]" with the "Match only whole words" option fails to return any results because the comma is considered part of the word.  If you uncheck the "Match only whole words" option, then SQL Search searches for the text "Column" and "1" and returns all results for either match.  Is there any way to replicate the "Exact Match" functionality in v3.1.6?
Tagged:

Answers

  • mdullni1mdullni1 Posts: 26 Bronze 3
    I second this issue. We need an exact match search.
  • HI @mjdiem @mdullni1

    Thanks for posting here.

    The development team has logged this issue as SDI-743 in our internal bug tracking system. They are investigating it, so please keep an eye on the release note!
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • Hi,

    Thanks for reaching out with your feedback; it's very helpful and I can certainly understand your use case!

    If the "Match whole words only" option ignored commas - that is, it would find "[column 1]," if you only searched for "column 1" - would that solve your problem? Or are there other reasons you find the "Match whole words only" option unsuitable? For example, any other punctuation that isn't a comma being treated as part of the word?

    Thanks again for your feedback on this!

    Asha
  • mdullni1mdullni1 Posts: 26 Bronze 3
    AshaPatel said:
    Hi,

    Thanks for reaching out with your feedback; it's very helpful and I can certainly understand your use case!

    If the "Match whole words only" option ignored commas - that is, it would find "[column 1]," if you only searched for "column 1" - would that solve your problem? Or are there other reasons you find the "Match whole words only" option unsuitable? For example, any other punctuation that isn't a comma being treated as part of the word?

    Thanks again for your feedback on this!

    Asha

    I am not using commas.  A statement such as ID = 3 does not work with match whole words only.
  • Hi @mdullni1,

    That's interesting, I am trying with statement "ID = 3" and I am successfully getting a match.



    Can you provide me with an example script for an object where you're seeing this problem?

    Thanks,
    Asha
  • mdullni1mdullni1 Posts: 26 Bronze 3

    Well, that wasn't working before but is working now.

    However, this does not work:   lngRcrdSttsID IN (

    So, maybe it is struggling with punctuation?

  • Hi @mdullni1,

    I assume in that situation you have some text immediately after the open bracket? The "Match whole words only" is designed to only match entire words; that is, match text beginning and ending with whitespace. So if you had an object where the definition was

    "lngRcrdSttsID IN ( MyObject"

    we would expect the "Match whole words only" option to correctly display this result when searching for "lngRcrdSttsID IN (". If however the object definition didn't have a space between the bracket, for example

    "lngRcrdSttsID IN (MyObject"

    then we wouldn't expect the "Match whole words only" option to display this. This result would be shown, however, when searching for "lngRcrdSttsID IN", or "lngRcrdSttsID IN (MyObject". Does this match what you are seeing?

    Are there many situations you have where it is necessary to search for something like "lngRcrdSttsID IN (" and searching for either "lngRcrdSttsID IN" or "lngRcrdSttsID IN (MyObject" instead wouldn't be suitable?

    Thanks,
    Asha
  • mdullni1mdullni1 Posts: 26 Bronze 3

    Searching for:

    lngRcrdSttsID in ( 1

    lngRcrdSttsID in (1

    lngRcrdSttsID in (

    All return nothing.  Yes, I could use just lngRcrdSttsID in if I did not want to specify the IN arguments.

    The point is that if the search includes punctuation, it fails.

  • Hi @mdullni1,

    I'm still not seeing the behaviour you're describing. For example, I created a stored procedure with four different select statements, each effectively selecting from a table where "ID IN (1)" but with spaces in different areas of the statement.

    When searching for "ID IN (" I saw two highlighted results - the results where there was a space before the number. This is expected behaviour:



    When searching for "ID IN (1" I saw one highlighted result - this is the only entry where the statement "ID IN (1" is followed by a space. This is also expected behaviour:



    Similarly, when searching for "ID IN ( 1" I saw again one highlighted result - this is the only entry where the statement "ID IN ( 1" is followed by a space. This is also expected behaviour:



    Have you got any scripts of an example of something that you would expect to be showing but isn't? Can you post any screenshots? 

    Thanks,
    Asha
  • mdullni1mdullni1 Posts: 26 Bronze 3
    edited July 10, 2018 12:39PM

    lngRcrdSttsID in (   returns nothing:

    lngRcrdSttsID in returns many, here is one:

  • AshaPatelAshaPatel Posts: 42 Bronze 5
    edited July 10, 2018 1:15PM
    Hi @mdullni1,

    The reason "lngRcrdSttsID in (" returns nothing in this case is because there isn't a space after the open bracket in your view. If your view was instead

    "WHERE lngRcrdSttsID in ( 1, 2)"

    with a space after the open bracket, then the result would be found. This is because the "Match whole words only" only matches whole words. SQL Search considers a word to be any text separated by a whitespace. So in your case, it treats "lngRcrdSttsID" as a word, "IN" as a word, "(1," as a word and "2)" as a word. As you are not searching for a whole word - because "(" isn't considered a whole word - you are not getting a result. 

    This would be an example where ignoring commas in the definition of a word may be useful to you, as we were discussing earlier in the thread. If SQL Search didn't include commas in its definition of a word, then instead of "(1," being defined as a word, "(1" would be defined as a word. Then, searching for "WHERE lngRcrdSttsID in (1" would return your result. If you turned the "Match whole words only" option off you would also see the result.

    Thanks,
    Asha
  • mdullni1mdullni1 Posts: 26 Bronze 3

    I really don't have anymore time for this.  The bottom line is that searching for the string lngRcrdSttsID in ( used to work. 

    What we need is a Match Whole String only.  There is no reason for it to parse out words.

  • Hi @mdullni1,

    We currently don't have any intention of introducing a "Match Whole String" option. If you want to make a suggestion for a new feature, please add it to our Uservoice panel.

    https://redgate.uservoice.com/forums/101149-sql-search

    Thanks,
    Asha
  • PowlinPowlin Posts: 42 Bronze 3

    Hi,

    I noticed the same bug with this option, if I search a field column when using the Match whole words only It only return when the column is separated by white space.

    The problem seem to be with the words separator, for example : @ [ ] ( ) , . etc. don't seem to be identified has word separator but they should.

    So for example using a column named : ServiceField_Indexed

    SELECT ServiceField_Indexed FROM Table : Work
    SELECT Table.ServiceField_Indexed FROM Table : Don't work
    SELECT [ServiceField_Indexed] FROM Table : Don't work
    SELECT UPDATE(ServiceField_Indexed) : Don't work
    SELECT ServiceField_Indexed, OtherField FROM Table : Don't work

    So this feature don't seem to be useful 90% of the time.

    Thanks

  • kalokalo Posts: 82 Bronze 5
    if you have same table name on different schemas then you cannot use the match whole words for the table-name as you need to include the schema, and then the search will return only the results for the single specified schema
  • kalokalo Posts: 82 Bronze 5
    And within dynamic sql I tend to remove whitespace in the query to reduce the length of the overall text so would have text as JOIN[dbo].[MyTable] : Searching for [dbo].[MyTable] doesn't find such instances with the match whole words on. But if i were to search for MyTable then i would get additional results i may not want for MyTableId  (column including the search test) or MyTableExtension (a different table that contains the search text)
  • JoojooflopJoojooflop Posts: 5 Bronze 2
    edited August 14, 2018 4:48PM
    I agree with other posters that this feature is now much less useful than it used to be.  The problem is with what is considered a "whole word."  This definition is, obviously, debatable, but I think the important question is which definition is most *useful*.  Using whitespace as the only delimiter is not very useful, in my opinion.

    For example, if I want to search for a column called ExampleColumn but ignore ExampleColumn2, BadExampleColumn, etc., I want it to find all of these:

      Select ExampleColumn From ExampleTable
      Select ExampleColumn, OtherColumn From ExampleTable
      Select [ExampleColumn] From ExampleTable
      Select et.ExampleColumn From ExampleTable et
      Select * From ExampleTable Where ExampleColumn=3

    but only the first one is currently found.


  • jim88888jim88888 Posts: 1 New member
    Why did you break this?  What did you expect to gain by breaking it?

    If I search for the following (where the underscores are spaces)

    abc_def

    and the database has

    123abc_defghi

    There should be a way to get it to match without it finding

    123_abc_zzz

    or 

    _def_

    If I select abc_def, it should match abc_def. Period.

    It really isn't that hard.

  • Currently the option is working as expected. For anyone who has feedback on this (or opinions on how the option should work instead) I would advise you to log a vote in the uservoice forums I linked to above. 

    We don't make changes without considering use cases. If you've experience working on a commercial product that relies on customer feedback you know that if you make changes to anything there are always going to be people who are unhappy as well as happy, and usually the people who are happy are quiet unless you change it again. You need to try to optimise the situation to maximise the happy people against the unhappy. The last thing you should do is make changes due to the opinions of the outspoken few - this is why we rely on uservoice, it allows us to get a much more general view of consensus.

    Thanks,
    Asha
  • JoojooflopJoojooflop Posts: 5 Bronze 2
    edited August 16, 2018 12:12PM
    Asha,
    I will do as you suggest and turn to the uservoice forums on this issue, but I have a question about this that I hope you can help with.  Given the new "match whole words only" behavior, how can I search for an object name (e.g. "SalesAmount"), while ignoring other object names that contain the name (e.g. "PriorSalesAmount")?

  • AshaPatel said:
    The last thing you should do is make changes due to the opinions of the outspoken few - this is why we rely on uservoice, it allows us to get a much more general view of consensus.

    Wow. Was there a User Voice item asking for "Match whole words only" to replace "Exact Match"???
    Seems like some outspoken few at RedGate decided to make this change.
    I'd love to see the "Use cases" where this is an improvement.

    For me, this is not an improvement. I've inherited a large set of stored procedures that contain things like:
    Exec Systemx.dbo.QueueEmail @Subject N'Failed to import from source'

    So a user sends me an email pointing out a problem, and I need to find where this is being generated from. SQL Search to the rescue? Nope. Searching "Failed to import from source" with 'Match whole words only' fails, because N' is considered as part of the word. Removing match whole words only produced a lot of noise.

    Lucky for me, I dump everything into git so I can use an external tool to search. (good thing it's not hung up words)
Sign In or Register to comment.