question about different query results with wildcard

Hi, I'm working with a third party app on SQL Server 2000, and from what I can gather, programmed in C# & VisualFoxPro.

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