Thunderstone Support Forums

I am having difficulty getting a SQL query like the following to work from the TSQL command line:
SQL 1>select StringValue from tTable where StringValue like 'abc%';

There is a metamorph inverted index on StringValue. I am finding that "like '%abc%'" works, but this is not what I want. Can you tell me what might be wrong?

Once I get this working how well can I expect a query like this to perform on a large table with approx. 1,000,000 rows?

Use * for wildcard, not %.

I having some trouble undertanding the use of the wildcard * character with the like statement.
With data similar to:

1 abc
2 abcde
3 abcdefg

I am expecting the query "like 'abcd*'" to return rows 2,3. Instead I am finding it returns all 3 rows. The same query without the wildcard appears to do the right thing which doesn't make sense to me. Also, the query is quite slow. Is the metamorph index being used?

If the data were what you describe the results would be as expected. But since the query is slow it must not be. What's your actual data and query look like?

A metamorph index would be used if available.

My actual data looks like this

table tCats
ObjectID int Path varchar(256)
1 \0\2
2 \0\2\4
3 \0\3\5
4 \0\3\6\7

There is a metamorph index on Path.
The query looks like "select * from tCats where Path like '\0\3*';"
I expect this query to return IDs 3 and 4, but it returns all rows.

Backslash is special in the query. You need to double it up: \\0\\3*
Also make sure you include \ in your index expression.

Or if you're always doing just prefix matching you could use a regular index and the matches operator instead. Backslash would not need doubling for matches: matches '\0\3%'

As you say I am always doing prefix matching. And I was mistaken when I showed a backslash in my example above. I am using forward slashes. Is the matches operator used with the "like" statement rather than the likep statement?

I've changed my data so that it looks precisely like the following
table tCats
ObjectID int Path varchar(256)
1 0.2
2 0.2.4
3 0.3.5
4 0.3.6.7

There is now a regular index on Path. I am finding that I get the results I want by not using either * or %, but I am concerned that my index is not being used because I am also able to get results on a substring search. For exampe "Path like '3.6'" is returning a row when I expected it not to.

You should be using
Path matches '3.6%'