|
 |
asp_databases thread: Re: Checking Case Sensitive Strings
Message #1 by Martin Lee <access@o...> on Thu, 16 May 2002 22:23:21 +0800
|
|
Thanks, works great.
Martin
At 04:26 AM 5/16/2002 +0000, you wrote:
>This is a good method. I can be made a bit better however. If you take the
>Username field in your table and make it varBinary, then create an index
>on it.
>
>Now you can use the same code as before except for this line:
>CAST(@UserName AS varBinary) = CAST(a.UserName as varBinary)
>
>Now you can use:
>Cast(@Username as varBinary) = a.Username
>
>In the sample code below you could not use an index because the values in
>the field had to be cast into a new data type. More than this though, all
>of the records would be converted and then compared. By changing the
>native data type in the table, you allow the use of an index AND you no
>longer convert every record. For a busy database it will make a big
>difference.
>
>
> > CAST the string as VARBINARY
>
>
>CREATE PROC usp_MySproc
>
> @UserName varChar(100)
>
>AS
>
> SELECT
> NULL
> FROM
> Users a
> WHERE
> CAST(@UserName AS varBinary) = CASE(a.UserName as varBinary)
>
>Cheers
>Ken
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>From: "Martin Lee" <access@o...>
>Subject: [asp_databases] Checking Case Sensitive Strings
>
>
>: is there similar T-SQL function as StrComp in VB?
>:
>: Am upgrading from Access2000 to MSSQL and need to implement case
>sensitive
>: passwords for a project. With Access used
>: ((StrComp([Password],[@Password],0))=0)) as part of the WHERE clause.
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
Message #2 by Martin Lee <access@o...> on Thu, 16 May 2002 20:40:31 +0800
|
|
Thanks for the replies, will give them a go.
Martin
At 04:26 AM 5/16/2002 +0000, you wrote:
>This is a good method. I can be made a bit better however. If you take the
>Username field in your table and make it varBinary, then create an index
>on it.
>
>Now you can use the same code as before except for this line:
>CAST(@UserName AS varBinary) = CAST(a.UserName as varBinary)
>
>Now you can use:
>Cast(@Username as varBinary) = a.Username
>
>In the sample code below you could not use an index because the values in
>the field had to be cast into a new data type. More than this though, all
>of the records would be converted and then compared. By changing the
>native data type in the table, you allow the use of an index AND you no
>longer convert every record. For a busy database it will make a big
>difference.
>
>
> > CAST the string as VARBINARY
>
>
>CREATE PROC usp_MySproc
>
> @UserName varChar(100)
>
>AS
>
> SELECT
> NULL
> FROM
> Users a
> WHERE
> CAST(@UserName AS varBinary) = CASE(a.UserName as varBinary)
>
>Cheers
>Ken
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>From: "Martin Lee" <access@o...>
>Subject: [asp_databases] Checking Case Sensitive Strings
>
>
>: is there similar T-SQL function as StrComp in VB?
>:
>: Am upgrading from Access2000 to MSSQL and need to implement case
>sensitive
>: passwords for a project. With Access used
>: ((StrComp([Password],[@Password],0))=0)) as part of the WHERE clause.
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
Message #3 by "Ken Schaefer" <ken@a...> on Thu, 16 May 2002 12:41:39 +1000
|
|
CAST the string as VARBINARY
CREATE PROC usp_MySproc
@UserName varChar(100)
AS
SELECT
NULL
FROM
Users a
WHERE
CAST(@UserName AS varBinary) = CASE(a.UserName as varBinary)
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Martin Lee" <access@o...>
Subject: [asp_databases] Checking Case Sensitive Strings
: is there similar T-SQL function as StrComp in VB?
:
: Am upgrading from Access2000 to MSSQL and need to implement case sensitive
: passwords for a project. With Access used
: ((StrComp([Password],[@Password],0))=0)) as part of the WHERE clause.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by sandy@m... on Thu, 16 May 2002 04:26:57
|
|
This is a good method. I can be made a bit better however. If you take the
Username field in your table and make it varBinary, then create an index
on it.
Now you can use the same code as before except for this line:
CAST(@UserName AS varBinary) = CAST(a.UserName as varBinary)
Now you can use:
Cast(@Username as varBinary) = a.Username
In the sample code below you could not use an index because the values in
the field had to be cast into a new data type. More than this though, all
of the records would be converted and then compared. By changing the
native data type in the table, you allow the use of an index AND you no
longer convert every record. For a busy database it will make a big
difference.
> CAST the string as VARBINARY
CREATE PROC usp_MySproc
@UserName varChar(100)
AS
SELECT
NULL
FROM
Users a
WHERE
CAST(@UserName AS varBinary) = CASE(a.UserName as varBinary)
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Martin Lee" <access@o...>
Subject: [asp_databases] Checking Case Sensitive Strings
: is there similar T-SQL function as StrComp in VB?
:
: Am upgrading from Access2000 to MSSQL and need to implement case
sensitive
: passwords for a project. With Access used
: ((StrComp([Password],[@Password],0))=0)) as part of the WHERE clause.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #5 by Martin Lee <access@o...> on Wed, 15 May 2002 21:53:20 +0800
|
|
Hi Again:)
is there similar T-SQL function as StrComp in VB?
Am upgrading from Access2000 to MSSQL and need to implement case sensitive
passwords for a project. With Access used
((StrComp([Password],[@Password],0))=0)) as part of the WHERE clause.
Thanks,
Martin
|
|
|
View Cart
