Searching character data using like

[Google User]

Hello All,

SQL 2000, case insensitive database

I have a situation where I need to find abbreviations in the rows in a
table. The rule i came up is, get all the rows from the table where
there is more than one character is capitalized consequtively eg.
"USA", "TIMS", "AIR"

Here is the sample data:

create table test (mystring varchar(100))
go
insert into test (mystring) values ('I live in USA')
insert into test (mystring) values ('this is a test row. usa
(abbreviated wrongly).')
go

--expected result set
mystring
----------------------
I live in USA

Here is the query which I tried.
select * from test where mystring collate SQL_Latin1_General_CP1_CS_AS
like '%[A-Z][A-Z]%'

But the above query returns both the records. Any help?

Thanks

[Gert-Jan Strik]

That's strange, I get the same result. However, if I change the query
(see below), I get the correct results (run on a database with
SQL_Latin1_General_CP1_CS_AS collation)

select * from test where mystring like
'%[ABCDEFGHIJKLMNOPQRSTUVWXYZ][ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'

Gert-Jan
Google User wrote:

Hello All,

SQL 2000, case insensitive database

I have a situation where I need to find abbreviations in the rows in a
table. The rule i came up is, get all the rows from the table where
there is more than one character is capitalized consequtively eg.
"USA", "TIMS", "AIR"

Here is the sample data:

create table test (mystring varchar(100))
go
insert into test (mystring) values ('I live in USA')
insert into test (mystring) values ('this is a test row. usa
(abbreviated wrongly).')
go

--expected result set
mystring
----------------------
I live in USA

Here is the query which I tried.
select * from test where mystring collate SQL_Latin1_General_CP1_CS_AS
like '%[A-Z][A-Z]%'

But the above query returns both the records. Any help?

Thanks

[Google User]

Thanks..
Both the the solutions worked fine. I was playing around with the
expression after I posted the message. I got it working as per
Gert-Jan solution even before the post got appeared in the news group.
I will change according to Sommars solution since it is more straight
forward. Thanks again for both of you.

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Google User (us*********@hotmail.com) writes:

Here is the query which I tried.
select * from test where mystring collate SQL_Latin1_General_CP1_CS_AS
like '%[A-Z][A-Z]%'

But the above query returns both the records. Any help?

This is because the range A-Z includes all characters in that range,
and SQL_Latin1_General_CP1_CS_AS is not ASCII, but the order is
AaBb....

This query cuts it:

select * from test where mystring
like '%[A-Z][A-Z]%' collate Latin1_General_BIN

I've chosen a binary collation. Also I've move the COLLATE expresion
to the search pattern, although it does not seem to make a difference.