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

mjdiem

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?

mdullni1

I second this issue. We need an exact match search.

Tianjiao_Li

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!

AshaPatel

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

mdullni1

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.

AshaPatel

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

mdullni1

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

However, this does not work:   lngRcrdSttsID IN (

So, maybe it is struggling with punctuation?

AshaPatel

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

mdullni1

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.

AshaPatel

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

mdullni1

lngRcrdSttsID in (   returns nothing:

lngRcrdSttsID in returns many, here is one:

AshaPatel

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

mdullni1

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.

AshaPatel

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

Powlin

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

kalo

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

kalo

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)

Joojooflop

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.

jim88888

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.

AshaPatel

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

Joojooflop

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")?

Sean_Lively

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)