case-sensitive searching in case-insensitive database

I recall reading about a way to check for a case-sensitive value in a
database that has a case-insensitive collation. Could anyone shed light on
this?
Thanks in advance.You could convert to binary, or use the COLLATE statement:
CREATE TABLE [dbo].[testCollation] (
[Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into testCollation (Col1Name)
values('FredBloggs')
go
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_ci_as
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_cs_as
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O19w0TVVEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You could convert to binary, or use the COLLATE statement:
> CREATE TABLE [dbo].[testCollation] (
> [Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into testCollation (Col1Name)
> values('FredBloggs')
> go
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_ci_as
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_cs_as
> HTH,
> Paul Ibison
>
If the table is large and the column indexed you may want to add a search
predicate for case-insensitive collation so you can use the index, and then
narrow it down further with the case-sensitive collation predicate.
select * from testCollation
where col1Name = 'fredBloggs'
and Col1Name = 'fredBloggs' collate latin1_general_cs_as
David

case-sensitive searching in case-insensitive database

I recall reading about a way to check for a case-sensitive value in a
database that has a case-insensitive collation. Could anyone shed light on
this?
Thanks in advance.
You could convert to binary, or use the COLLATE statement:
CREATE TABLE [dbo].[testCollation] (
[Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into testCollation (Col1Name)
values('FredBloggs')
go
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_ci_as
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_cs_as
HTH,
Paul Ibison
|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O19w0TVVEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You could convert to binary, or use the COLLATE statement:
> CREATE TABLE [dbo].[testCollation] (
> [Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into testCollation (Col1Name)
> values('FredBloggs')
> go
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_ci_as
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_cs_as
> HTH,
> Paul Ibison
>
If the table is large and the column indexed you may want to add a search
predicate for case-insensitive collation so you can use the index, and then
narrow it down further with the case-sensitive collation predicate.
select * from testCollation
where col1Name = 'fredBloggs'
and Col1Name = 'fredBloggs' collate latin1_general_cs_as
David

case-sensitive searching in case-insensitive database

I recall reading about a way to check for a case-sensitive value in a
database that has a case-insensitive collation. Could anyone shed light on
this?
Thanks in advance.You could convert to binary, or use the COLLATE statement:
CREATE TABLE [dbo].[testCollation] (
[Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into testCollation (Col1Name)
values('FredBloggs')
go
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_ci_as
select * from testCollation
where Col1Name = 'fredBloggs' collate latin1_general_cs_as
HTH,
Paul Ibison|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O19w0TVVEHA.1152@.TK2MSFTNGP09.phx.gbl...
> You could convert to binary, or use the COLLATE statement:
> CREATE TABLE [dbo].[testCollation] (
> [Col1Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
> ) ON [PRIMARY]
> GO
> insert into testCollation (Col1Name)
> values('FredBloggs')
> go
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_ci_as
> select * from testCollation
> where Col1Name = 'fredBloggs' collate latin1_general_cs_as
> HTH,
> Paul Ibison
>
If the table is large and the column indexed you may want to add a search
predicate for case-insensitive collation so you can use the index, and then
narrow it down further with the case-sensitive collation predicate.
select * from testCollation
where col1Name = 'fredBloggs'
and Col1Name = 'fredBloggs' collate latin1_general_cs_as
David

Case-Sensitive Search

Hi,
We have two systems, a Financial system and a Professional Service Automated
system. The database currently has a collation of case-insensitive
(MSSQL2K). The Financial system happened to have incorrectly exported a
ProjectCode value that was in all lower case to the other system where in
expects a ProjectCode value to be in UPPERCASE. I would to be able to write
a query that will identify all rows in a table in the financial system where
the ProjectCode is in lowercase in the case-insensitive environment.
TIA,
bpdeeSee "collate" clause in BOL.
Example:
select
*
from
(
select 1, cast('microsoft sql server 2000' as varchar(25))
union all
select 2, cast('MICROSOFT SQL SERVER 2000' as varchar(25))
) t1(c1, c2)
where
c2 like 'microsoft sql server 2000' collate SQL_Latin1_General_CP1_CS_AS
AMB
"bpdee" wrote:
> Hi,
> We have two systems, a Financial system and a Professional Service Automated
> system. The database currently has a collation of case-insensitive
> (MSSQL2K). The Financial system happened to have incorrectly exported a
> ProjectCode value that was in all lower case to the other system where in
> expects a ProjectCode value to be in UPPERCASE. I would to be able to write
> a query that will identify all rows in a table in the financial system where
> the ProjectCode is in lowercase in the case-insensitive environment.
> TIA,
> bpdee