Sort Order and case sensitivity

[Steve]

I have a query which filters records containing uppercase and
Lowercase i.e.

Smith and SMITH, Henderson and HENDERSON etc.

Is there a way that I can filter only those records that contain the
first uppercase letter and the remaining lowercase letters for my
query i.e. Smith , HENDERSON etc.

Thanks

[Erland Sommarskog]

Re: Sort Order and case sensitivity

Steve (murras68@hotma il.com) writes:[color=blue]
> I have a query which filters records containing uppercase and
> Lowercase i.e.
>
> Smith and SMITH, Henderson and HENDERSON etc.
>
> Is there a way that I can filter only those records that contain the
> first uppercase letter and the remaining lowercase letters for my
> query i.e. Smith , HENDERSON etc.[/color]

You can do this (example runs in Northwind):

SELECT *
FROM Customers
WHERE CompanyName COLLATE Latin1_General_ BIN LIKE '[A-ZÀ-Ý]%'
AND CompanyName COLLATE Latin1_General_ BIN NOT LIKE '_%[A-ZÀ-Ý]%'

The requirements is somewhat relaxed here. You will actually get
hits for "J1234" or "D....". Depending on your data, this may or
may not help.

Please note that it is not likely that SQL Server will use an index
for this search.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Steve]

Re: Sort Order and case sensitivity

Thanks for the tip, I have had a problem in trying to using it in
SQL2000 as I get an error regarding the COLLATE function. My database
in case insensitive and accent insensitive, I'm wondering if this may
have something to do with it.

Regards

Steve

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns944858 DFC6C2Yazorman@ 127.0.0.1>...[color=blue]
> Steve (murras68@hotma il.com) writes:[color=green]
> > I have a query which filters records containing uppercase and
> > Lowercase i.e.
> >
> > Smith and SMITH, Henderson and HENDERSON etc.
> >
> > Is there a way that I can filter only those records that contain the
> > first uppercase letter and the remaining lowercase letters for my
> > query i.e. Smith , HENDERSON etc.[/color]
>
> You can do this (example runs in Northwind):
>
> SELECT *
> FROM Customers
> WHERE CompanyName COLLATE Latin1_General_ BIN LIKE '[A-ZÀ-Ý]%'
> AND CompanyName COLLATE Latin1_General_ BIN NOT LIKE '_%[A-ZÀ-Ý]%'
>
> The requirements is somewhat relaxed here. You will actually get
> hits for "J1234" or "D....". Depending on your data, this may or
> may not help.
>
> Please note that it is not likely that SQL Server will use an index
> for this search.[/color]

[Erland Sommarskog]

Re: Sort Order and case sensitivity

Steve (murras68@hotma il.com) writes:[color=blue]
> Thanks for the tip, I have had a problem in trying to using it in
> SQL2000 as I get an error regarding the COLLATE function. My database
> in case insensitive and accent insensitive, I'm wondering if this may
> have something to do with it.[/color]

Rather than saying that you get an error, it would be somewhat easier
for me to say what is the problem, if you also included the error message.
Of course, also the exact statement you are using would be helpful.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Steve]

Re: Sort Order and case sensitivity

Hi

Here is the error I recieve when trying to run the code in SQL


[Microsoft][ODBC SQL Sever Driver][SQL Server]Line 1 : Incorrect
syntax near 'COLLATE'

Regards

Steve

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns944A95 DF6AD7CYazorman @127.0.0.1>...[color=blue]
> Steve (murras68@hotma il.com) writes:[color=green]
> > Thanks for the tip, I have had a problem in trying to using it in
> > SQL2000 as I get an error regarding the COLLATE function. My database
> > in case insensitive and accent insensitive, I'm wondering if this may
> > have something to do with it.[/color]
>
> Rather than saying that you get an error, it would be somewhat easier
> for me to say what is the problem, if you also included the error message.
> Of course, also the exact statement you are using would be helpful.[/color]

[Erland Sommarskog]

Re: Sort Order and case sensitivity

Steve (murras68@hotma il.com) writes:[color=blue]
> Here is the error I recieve when trying to run the code in SQL
>
>
> [Microsoft][ODBC SQL Sever Driver][SQL Server]Line 1 : Incorrect
> syntax near 'COLLATE'[/color]

Since you did not provide the statment, I will have to guess. Assuming
that you used the statement that I gave as example, my guess is that
your database is not at compability level 80. COLLATE was added to
SQL 2000, so if your database is set at backward compatinility, COLLATE
is not available.

You can use sp_dbcmplevel to both determine the compatibility level and to
change it.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Steve]

Re: Sort Order and case sensitivity

Hi,

The compability level was 70.

Thanks

Steve

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns944B83 AD61726Yazorman @127.0.0.1>...[color=blue]
> Steve (murras68@hotma il.com) writes:[color=green]
> > Here is the error I recieve when trying to run the code in SQL
> >
> >
> > [Microsoft][ODBC SQL Sever Driver][SQL Server]Line 1 : Incorrect
> > syntax near 'COLLATE'[/color]
>
> Since you did not provide the statment, I will have to guess. Assuming
> that you used the statement that I gave as example, my guess is that
> your database is not at compability level 80. COLLATE was added to
> SQL 2000, so if your database is set at backward compatinility, COLLATE
> is not available.
>
> You can use sp_dbcmplevel to both determine the compatibility level and to
> change it.[/color]