How to Find Extended Characters in Text

[HumanJHawkins]

Hi,

I need to find records where a text (and/or varchar) field contains
extended characters. If I knew particular extended characters, I could
do something like this:
select * from TableName where charindex('—',vchitem)<>0 or charindex
('°',vchitem)<>0

But I do not know what extended characters might be in the field, so I
need to do something like looking for text that contains an ASCII
value greater than 127 for example.

Can anyone think of how to do this?
Thanks in advance.

[Erland Sommarskog]

HumanJHawkins (JHaw...@Locutius.Com) writes:
> I need to find records where a text (and/or varchar) field contains
> extended characters. If I knew particular extended characters, I could
> do something like this:

> select * from TableName where charindex('-',vchitem)<>0 or charindex

> ('',vchitem)<>0
>
> But I do not know what extended characters might be in the field, so I
> need to do something like looking for text that contains an ASCII
> value greater than 127 for example.
>
> Can anyone think of how to do this?

patindex('%[' + char(127) + '-' + char(255) + ']%',
col COLLATE Latin1_General_BIN2)

That is, by forcing a binary collection, you can use a range pattern.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[HumanJHawkins]

On Nov 12, 3:28 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
<CUT>

> patindex('%[' + char(127) + '-' + char(255) + ']%',
>           col COLLATE Latin1_General_BIN2)
>
> That is, by forcing a binary collection, you can use a range pattern.

Thanks. It worked perfectly (I used "Latin1_General_BIN" instead of
"Latin1_General_BIN2", as I'm on SQL2000)