Password Matching
-
how can i compare two strings in SqlServer.
I need to distinguish between upper case string and a lower case.
-
Best method is to use a case sensitive collation
SELECT * FROM ::fn_helpcollations()
Will show you the different collations that are available.
Otherwise you'd be stuck with using something like the ASCII function,
SELECT ASCII('a'), ASCII('A') WHERE 'A' = 'a'SELECT ASCII('a'), ASCII('A') WHERE ASCII('A') = ASCII('a')--------------------
Colt 45 - the original point and click interface
-
can u be little more clear.
can u post me an example.
thank u
-
Hmmm ... I thought it was very clear given your original post ...
What part wasn't clear?
--------------------
Colt 45 - the original point and click interface -
i need an example.
how to use the function and all that.
can u send me a script plz.
-thank u
-
Ok I'm confused ... I thought I included an example ... did you see that part highlighted in blue?
Maybe if you provide an example of the strings you're trying to compare it might help?
Also, what collation are you using for the database?
--------------------
Colt 45 - the original point and click interface -
When a table column is defined with a case insensitive collation but you want comparisons to use a different collation, the desired collation can be specified within the SQL.
Easier might be to alter the table column collation
specification once and then all comparisons will use the rules of the desired collation.
Create table Foo
(d1varchar(255) collate SQL_Latin1_General_CP1_CI_AI not null
-- Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive
--, width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page 1252 for non-Unicode Data
)
go
-- Add lower and upper case values
Insert into Foo (d1) values ('a')
go
Insert into Foo (d1) values ('A')
go
-- Case insensitive
select Foo1.d1, Foo2.d1
from Foo as Foo1
joinFoo as Foo2
on Foo1.d1 = Foo2.d1
go
-- Case Sensitive
-- Change Collation within SQL
select Foo1.d1, Foo2.d1
from Foo as Foo1
joinFoo as Foo2
on Foo1.d1 collate SQL_Latin1_General_CP1_CS_AS
= Foo2.d1 collate SQL_Latin1_General_CP1_CS_AS
go
-- Change collation permanently to case sensitive
alter table foo
alter column d1varchar(255) collate SQL_Latin1_General_CP1_CS_AS
go
-- Case sensitive
select Foo1.d1, Foo2.d1
from Foo as Foo1
joinFoo as Foo2
on Foo1.d1 = Foo2.d1
go
SQL = Scarcely Qualifies as a Language
-
Thanx Carl.
U got me correctly.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply