DennBen (dbenedett@hotmail.com) writes:
> That piece of code is pretty cool, and I'm not sure what COLLATE
> Latin1_General_BIN. I couldnt find any good documentation on it.

The COLLATE clause is documented in Books Online. What I do is
that I force a binary collation, so that I can use an ASCII range
in the [] range. This illustrates:

CREATE TABLE ulf(a varchar(20) NOT NULL)
go
INSERT ulf(a) VALUES ('Albin')
INSERT ulf(a) VALUES ('alldaglig')
INSERT ulf(a) VALUES ('Per')
INSERT ulf(a) VALUES ('spårvagn')
INSERT ulf(a) VALUES ('Hansson')
INSERT ulf(a) VALUES ('folkhem')
go
SELECT a FROM ulf WHERE a LIKE '[A-Z]%'
SELECT a FROM ulf WHERE a COLLATE Latin1_General_BIN LIKE '[A-Z]%'
go
DROP TABLE ulf

The first SELECT will return 5 or 6 rows depending on your database
collation, because the range A-Z expands to AbBC ...zZ. The second
SELECT returns only three rows, because by forcing a binary collation
strict ASCII order is applied.

> However, I tried it out for my purpose and it selects false positives.
> It will select characters like apostrophe's that are valid utf-8
> characters (less than ASCII value - char(188). can you point to a
> site that would allow me to get a better understanding of the code you
> offered, and in so doing I might be able to tweak it a bit...?

In that case you need to explain more clearly. If your post you said
ASCII code >= 128, and 188 was > 188 last time I looked. Besides, 188
is ONE QUARTER and not an apostrophe.

Also, keep in mind that SQL Server not support storing UTF-8 data. You
can always push down the bytes, but SQL Server will not understand what's
going on.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx