When we search with
Note contains 94949
we get 571 results, when we search with
Note contains 94949*
we get 575 results.
There should be at least a hundred different entries that start with "94949-1" so I expected the query with the wildcard to return something like 680 results, not an additional four rows.
Searching with
Note contains 94949-1*
got 483 results
Note contains 94949-10*
got 0 results
Could someone explain or point me to more documentation on the difference results we get?
Thanks
To see the 'wildcards' used with T-SQL, look up topic: LIKE in Books Online.
The asterisk is not a T-SQL wildcard. You queries using an asterisk are most likely returning you counts where the last character is an asterisk. AND you MUST use the LIKE equality instead of equals (=).
Here is a small example of using Wildcards in T-SQL:
SET NOCOUNT ON
CREATE TABLE #MyTable
( MyTableID int IDENTITY,
MyString varchar(50)
)
INSERT INTO #MyTable VALUES ( '1234' )
INSERT INTO #MyTable VALUES ( '1234-1' )
INSERT INTO #MyTable VALUES ( '1234*' )
INSERT INTO #MyTable VALUES ( '1234-45' )
INSERT INTO #MyTable VALUES ( '1234-ab' )
SELECT '1234', count(1) FROM #MyTable WHERE MyString = '1234'
SELECT '1234*', count(1) FROM #MyTable WHERE MyString = '1234*'
SELECT 'LIKE ''1234''', count(1) FROM #MyTable WHERE MyString LIKE '1234'
SELECT 'LIKE ''1234%''', count(1) FROM #MyTable WHERE MyString LIKE '1234%'
DROP TABLE #MyTable
|||Hi Arnie, thanks for your response.My situation is more basic than what appears in your example. Unfortunately, I am working solely through this application so I do not have access to the SQL to test out what you supplied, so my question is more of a need for an explanation of the search results. I do find lots of references to LIKE, Wildcards and pattern matching, but I don't find a way to explain to the users the best and most complete way to search. They mostly need a "this will always get us the results without missing anything" search technique and then to be able to select from a smaller group. I guess I need a basic course in understanding search results: how to get different ones and what they mean.
Using
Note contains 94949-'%'
returned one more result than when using an asterisk. I don't understand this difference.
Note contains 94949-'%1' or
Note contains 94949-'1%'
brings nothing nor does not using quotes. But there are hundreds of records which have the string starting with 94949-1 and a varying number of characters after that.
?Does the dash read not as a character in the string but as an expression?
When I use WITHIN 3 characters, I get too few results (eight). If I use AND, I get text unrelated to the account number I am looking for.
Again when I tried to narrow the search by adding one digit to the string to be matched, I did not get any results, but 500 results from the more general search is too much to scan by opening individual records.
Thanks for pondering this with me.
|||Assuming that the application is using SQL Server Full-Text Indexing, the rules would be defined here
http://msdn2.microsoft.com/en-us/library/ms187787.aspx
