Does anyone know how to query a field in a table where it contains an
ASCII code >= 128 - without looping through every field for every
record in table (using charindex)?

Ex of char I would like to find: ü which is char(252)

DennBen (dbenedett@hotmail.com) writes:
> Does anyone know how to query a field in a table where it contains an
> ASCII code >= 128 - without looping through every field for every
> record in table (using charindex)?
>
> Ex of char I would like to find: ü which is char(252)

select *
from tbl
where col COLLATE Latin1_General_BIN
LIKE '%[^' + char(32) + '-' + char(126) + ']%'

If you want to run this for many in columns in many tables, you
will to run the query once per column and table.

--
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

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.
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...?

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