Forzar al sqlserver a distinguir entre mayusculas y minúsculas

27/05/2004 - 18:00 por microsoft | Informe spam
Pues eso, quiero dar credenciales a partir de si cierta contraseña dada x un
usuario es correcta, pero quiero respetar la distinción entre mayúsculas y
minúsculas y por defecto el sql no las distingue.. alguien sabe como
hacerlo?

Preguntas similare

Leer las respuestas

#1 Emilio Boucau
27/05/2004 - 19:19 | Informe spam
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
Respuesta Responder a este mensaje
#2 microsoft
27/05/2004 - 20:36 | Informe spam
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


email Siga el debate Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaRespuesta Tengo una respuesta
Search Busqueda sugerida