Problems with: SELECT ... WHERE ... LIKE 'S%' ...

pdj
pdj Member Posts: 643
edited 2011-11-14 in SQL General
(Using R2 - Classic - reproduced in several builds, most recent is 32813)

Encountered a strange problem today ](*,)

The Customer is in the Item List setting a filter on Item No. like this: "S*" (All Item Numbers starting with capital S)
This result is fine, until the user clicks Ctrl-Home to go to the top of the list. Then all records disappear :-k
It works fine for other letters, it seems to be a problem with S.

Did some testing and I can reproduce the problem in all databases using the Danish Windows collation (CS and AS). In databases using a SQL-Collation it works fine, so it seems collation related.

I then used Client Monitor to look at the SQL statement:
SELECT * FROM "MyCompany$Standard Text"
WHERE (("Code" LIKE 'S%'))
ORDER BY "Code" DESC OPTION (OPTIMIZE FOR UNKNOWN,FAST 10)

Executing this statement in a query window in SSMS resulted in an empty dataset. Doing it without the WHERE clause, or with a different letter all seems to work fine.
Examples:
'S%' - Incorrectly always empty
'HS%' - Incorrectly always empty
'SE%' - Ok
'%S%' - Ok

What the h... is up with SQL and S% :shock:
Regards
Peter

Comments

Categories