compare two string in SQL Server

1 Sep 2005 10:19 AM

Martin

Hi,
I am writing a Store Procedure for Login, as following:

@p_sUsername, @p_sPassword are parameters
....
SELECT @BName = B.username, @BPW = Password FROM BENUTZER as B WHERE
username = @p_sUsername AND Passwort = @p_sPassword
....

but the SELECT Statement can not differ Uppercase and Lowercase, that means,
"Martin" = "martin"

Then I check explicitly:

if @BName != @p_sUsername
Login = 0

but this comparing works the same.

What can I do?

Thanks
Martin

1 Sep 2005 10:32 AM

Jens SÃ¼ÃŸmeyer

if you SQL Server database is not case sensitive you have to convert to to a
comparable format ,e.g. varbinary or specifiy a CASE Sensitive (CS) Collation
for it:

Select 1 where 'test' = 'TEST'
GO

Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST' COLLATE
SQL_Latin1_General_CP1_CS_AS
GO
Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
Select 1 where 'test' = 'TEST'
GO

Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST' COLLATE
SQL_Latin1_General_CP1_CS_AS
GO
Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)

--
HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"Martin" wrote:

> Hi,
> I am writing a Store Procedure for Login, as following:
>
> @p_sUsername, @p_sPassword are parameters
> ....
> SELECT @BName = B.username, @BPW = Password FROM BENUTZER as B WHERE
> username = @p_sUsername AND Passwort = @p_sPassword
> ....
>
> but the SELECT Statement can not differ Uppercase and Lowercase, that means,
> "Martin" = "martin"
>
> Then I check explicitly:
>
> if @BName != @p_sUsername
> Login = 0
>
> but this comparing works the same.
>
> What can I do?
>
> Thanks
> Martin
>
>
>

1 Sep 2005 11:52 AM

Martin

thanks
Martin

"Jens Süßmeyer" <Jens@[Remove_that][for contacting me]sqlserver2005.de>

schrieb im Newsbeitrag
Show quote

news:877CE479-F0DF-4CDA-8720-A14908BFB207@microsoft.com...
> if you SQL Server database is not case sensitive you have to convert to to
a
> comparable format ,e.g. varbinary or specifiy a CASE Sensitive (CS)
Collation
> for it:
>
> Select 1 where 'test' = 'TEST'
> GO
>
>
>
> Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST'
COLLATE
> SQL_Latin1_General_CP1_CS_AS
> GO
> Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
> Select 1 where 'test' = 'TEST'
> GO
>
>
>
> Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST'
COLLATE
> SQL_Latin1_General_CP1_CS_AS
> GO
> Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
> --
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
>
> "Martin" wrote:
>
> > Hi,
> > I am writing a Store Procedure for Login, as following:
> >
> > @p_sUsername, @p_sPassword are parameters
> > ....
> > SELECT @BName = B.username, @BPW = Password FROM BENUTZER as B WHERE
> > username = @p_sUsername AND Passwort = @p_sPassword
> > ....
> >
> > but the SELECT Statement can not differ Uppercase and Lowercase, that
means,
> > "Martin" = "martin"
> >
> > Then I check explicitly:
> >
> > if @BName != @p_sUsername
> > Login = 0
> >
> > but this comparing works the same.
> >
> > What can I do?
> >
> > Thanks
> > Martin
> >
> >
> >

1 Sep 2005 10:37 AM

David Portas

Case-sensitivity is determined by the column collation. So choose a
case-sensitive collation. For example:

ALTER TABLE benutzer
ALTER COLUMN username VARCHAR(128) COLLATE Latin1_General_CS_AS ;

--
David Portas
SQL Server MVP
--

1 Sep 2005 11:51 AM

Martin

Thanks
Martin

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> schrieb im
Newsbeitrag news:1125571037.540712.199690@g43g2000cwa.googlegroups.com...
> Case-sensitivity is determined by the column collation. So choose a
> case-sensitive collation. For example:
>
> ALTER TABLE benutzer
> ALTER COLUMN username VARCHAR(128) COLLATE Latin1_General_CS_AS ;
>
> --
> David Portas
> SQL Server MVP
> --
>

1 Sep 2005 12:51 PM

Dan Guzman

To add to the other responses, if you force a case-sensitive compare in your
SQL statement, consider also including the normal compare in your WHERE
clause. This will allow SQL Server to efficiently use indexes on those
columns and thereby improve performance.

SELECT
    @BName = B.username,
    @BPW = Password
FROM BENUTZER as B
WHERE
    username COLLATE SQL_Latin1_General_CP1_CS_AS = @p_sUsername AND
    username = @p_sUsername AND
    password COLLATE SQL_Latin1_General_CP1_CS_AS = @p_sPassword AND
    Password = @p_sPassword

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Martin" <marti***@freenet.de> wrote in message
news:Okeml6trFHA.304@TK2MSFTNGP11.phx.gbl...
> Hi,
> I am writing a Store Procedure for Login, as following:
>
> @p_sUsername, @p_sPassword are parameters
> ...
> SELECT @BName = B.username, @BPW = Password FROM BENUTZER as B WHERE
> username = @p_sUsername AND Passwort = @p_sPassword
> ...
>
> but the SELECT Statement can not differ Uppercase and Lowercase, that
> means,
> "Martin" = "martin"
>
> Then I check explicitly:
>
> if @BName != @p_sUsername
> Login = 0
>
> but this comparing works the same.
>
> What can I do?
>
> Thanks
> Martin
>
>