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_Gene ral_CP1_CS_AS
like '%[A-Z][A-Z]%'

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

Thanks

[Gert-Jan Strik]

Re: Searching character data using like

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_Gene ral_CP1_CS_AS collation)

select * from test where mystring like
'%[ABCDEFGHIJKLMNO PQRSTUVWXYZ][ABCDEFGHIJKLMNO PQRSTUVWXYZ]%'

Gert-Jan


Google User wrote:[color=blue]
>
> 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_Gene ral_CP1_CS_AS
> like '%[A-Z][A-Z]%'
>
> But the above query returns both the records. Any help?
>
> Thanks[/color]

[Google User]

Re: Searching character data using like

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 <sommar@algonet .se> wrote in message news:<Xns93C2EC CFE3863Yazorman @127.0.0.1>...[color=blue]
> Google User (user_google@ho tmail.com) writes:[color=green]
> > Here is the query which I tried.
> > select * from test where mystring collate SQL_Latin1_Gene ral_CP1_CS_AS
> > like '%[A-Z][A-Z]%'
> >
> > But the above query returns both the records. Any help?[/color]
>
> This is because the range A-Z includes all characters in that range,
> and SQL_Latin1_Gene ral_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.[/color]