Thunderstone Support Forums

Below is a list of search terms that I have questions about.
select * from mytable where FIELD1/FIELD2/FIELD3~n like:
1) '(1 or 9)' returned 13 hits
2) '1 or 9' returned 175,345 hits
3) '(1,9)' returned 500,453 hits (no blank space between characters)
4) '(1 , 9)' returned 527,875 hits. (blank spaces exist)

I would like to know why #2,#3 and #4 are producing different hit numbers. Please note that All fields are set to data type Varchar(255)

TIA.

"like" uses set logic, not boolean. Noise words such as "and" and "or" are ignored. See the manual about Metamorph queries.

1) (1 or 9) means find the phrase "1 or 9"
2) 1 or 9 means find "1" and "9"
3) (1,9) means find "1" or "9". Parens are a method of entering equivs in the query and can generally be thought of as a boolean "or". Don't include any extra spaces.
4) (1 , 9) means find "1 " or " 9" (spaces are kept)

Say I have to records in my table:

ID TITLE
EEM001 petersen-r
EEM002 petersen-ro

If I run this query in tsql:
select ID from mytable where TITLE like 'petersen-r'

I get both records returned.

If I run this:
select ID from mytable where TITLE like '\"petersen-r\"'

I get the first ID returned.

However, if I run this:

select ID from mytable where TITLE like '\"petersen-r*\"'

both IDs are returned. I thought because of the double-quotes I'd be looking for exactly what's inbetween (zero hits in this case), but I guess not.

Is this due to the LIKE clause interacting with the asterisk? I assume so. If so, is there a way to search for "petersen-r*" while still using LIKE-- so that it can match that phrase in the middle of some other text for instance, where "=" won't cut it.

I don't know in what context you're performing those searches because the \ is not generally valid.

All quotes do is bind phrase words together. They do not affect interpretation of what's inside.
abc-def is the same as "abc def" (unless you've turned off hyphenphrase).

TITLE like 'petersen-r*'