Forzar al sqlserver a distinguir entre mayusculas y minúsculas

Hola,

a lo mejor esto te sirve ...

Case-Sensitive Comparisons and Sorts on a Case-Insensitive Server

Despite the reason(s) you had for setting up your SQL Server
case-insensitive, you may occasionally need to do case-sensitive comparisons
and sorts. The varbinary data type can accomplish this. Here's a simple
table to illustrate:



create table vend(v_code varchar(10) not null)

insert vend(v_code) values('TRAMI') -- All caps could be legacy data
insert vend(v_code) values('110WS')
insert vend(v_code) values('ZACCA')
insert vend(v_code) values('TowUs')
insert vend(v_code) values('TraMi') -- This column isn't unique.
insert vend(v_code) values('belso')
insert vend(v_code) values('Tax2000')
insert vend(v_code) values('Tax2000')



Say we want to look at codes that are all uppercase. We can't do it like
this; this query will return all the rows:



select *
from vend
where v_code = upper(v_code)



Let's rewrite it using varbinary:



select *
from vend
where cast(v_code as varbinary) = cast(upper(v_code) as varbinary)



'TraMi" can be distinguished from "TRAMI" with the following query:



select *
from vend
where v_code = 'TraMi' -- Use an index if available. Likely superfluous with
7.0.
and cast(v_code as varbinary) = cast('TraMi' as varbinary)



If you need to do this frequently, you can save some typing by adding a
computed column to the table.



alter table vend
add varb as cast(v_code as varbinary)
go

select v_code
from vend
where varb = cast('TraMi' as varbinary)



Computed columns can't be indexed, at least in SQL Server 7.0, so you should
try to give the optimizer something to chew on. The server is very smart and
will pick a good plan without your help if usable indexes and statistics are
available, but it's worthwhile to experiment and tweak with the Query
Analyzer, and perhaps even the Profiler.

Also, I'm using the default length for varbinary. See BOL. Either provide
for adequate size or risk subtle errors.



For the conclusion of our varbinary tour, let's get a sorted, case-sensitive
list:

select v_code
from vend
group by varb, v_code
order by varb



Saludos !

Emilio Boucau
Buenos Aires - Argentina
http://www.portalsql.com

Ok muchas gracias voy a ver Emilio
;)

"Emilio Boucau" escribió en el mensaje
news:

Hola,

a lo mejor esto te sirve ...

Case-Sensitive Comparisons and Sorts on a Case-Insensitive Server

Despite the reason(s) you had for setting up your SQL Server
case-insensitive, you may occasionally need to do case-sensitive

comparisons

and sorts. The varbinary data type can accomplish this. Here's a simple
table to illustrate:



create table vend(v_code varchar(10) not null)

insert vend(v_code) values('TRAMI') -- All caps could be legacy data
insert vend(v_code) values('110WS')
insert vend(v_code) values('ZACCA')
insert vend(v_code) values('TowUs')
insert vend(v_code) values('TraMi') -- This column isn't unique.
insert vend(v_code) values('belso')
insert vend(v_code) values('Tax2000')
insert vend(v_code) values('Tax2000')



Say we want to look at codes that are all uppercase. We can't do it like
this; this query will return all the rows:



select *
from vend
where v_code = upper(v_code)



Let's rewrite it using varbinary:



select *
from vend
where cast(v_code as varbinary) = cast(upper(v_code) as varbinary)



'TraMi" can be distinguished from "TRAMI" with the following query:



select *
from vend
where v_code = 'TraMi' -- Use an index if available. Likely superfluous

with

7.0.
and cast(v_code as varbinary) = cast('TraMi' as varbinary)



If you need to do this frequently, you can save some typing by adding a
computed column to the table.



alter table vend
add varb as cast(v_code as varbinary)
go

select v_code
from vend
where varb = cast('TraMi' as varbinary)



Computed columns can't be indexed, at least in SQL Server 7.0, so you

should

try to give the optimizer something to chew on. The server is very smart

and

will pick a good plan without your help if usable indexes and statistics

are

available, but it's worthwhile to experiment and tweak with the Query
Analyzer, and perhaps even the Profiler.

Also, I'm using the default length for varbinary. See BOL. Either provide
for adequate size or risk subtle errors.



For the conclusion of our varbinary tour, let's get a sorted,

case-sensitive

list:

select v_code
from vend
group by varb, v_code
order by varb



Saludos !

Emilio Boucau
Buenos Aires - Argentina
http://www.portalsql.com