"Match whole words only" vs. "Exact Match"
mjdiem Posts: 1 New member
in SQL Search
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?
1 · Share on Twitter
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!
Tianjiao Li | Redgate Software
Have you visited our Help Center?
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!
I am not using commas. A statement such as ID = 3 does not work with match whole words only.
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?
Well, that wasn't working before but is working now.
However, this does not work: lngRcrdSttsID IN (
So, maybe it is struggling with punctuation?
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?
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.
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?
lngRcrdSttsID in ( returns nothing:
lngRcrdSttsID in returns many, here is one:
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.
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.
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.
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.
Select [ExampleColumn] From ExampleTable
If I search for the following (where the underscores are spaces)
and the database has
There should be a way to get it to match without it finding
If I select abc_def, it should match abc_def. Period.
It really isn't that hard.
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.
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")?
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)