April 11, 2019 at 10:58 am
This behavior:
SELECT V.String,
CASE WHEN V.String COLLATE Latin1_General_100_BIN LIKE '%[a-z]%' THEN 'Yes' ELSE 'No' END AS ContainsLowerCase
FROM (VALUES('This has lowercase letters'),('THIS HAS NO LOWERCASE LETTERS'))V(String);
SELECT V.String,
CASE WHEN V.String COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%[a-z]%' THEN 'Yes' ELSE 'No' END AS ContainsLowerCase
FROM (VALUES('This has lowercase letters'),('THIS HAS NO LOWERCASE LETTERS'))V(String);CS in the collation name means case sensitive
It does, but the second statement will return "yes" for both strings as the collation SQL_Latin1_General_CP1_CS_AS sorts the characters in the order AaBbCc...Zz, so the upper case betters B - Z are "between" the lowercase letters a-z. On the other hand the character sorting order for Latin1_General_100_BIN is ABC...YZabc...yz, hence the difference in result. The above is expected behaviour.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 11, 2019 at 11:07 am
This can perhaps be seen better with the below statement:
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS I
FROM N N1
CROSS JOIN N N2),
C AS(
SELECT CHAR(65+I) AS C
FROM Tally
WHERE I BETWEEN 0 AND 25
OR I BETWEEN 32 AND 57)
SELECT C.C AS Character,
ROW_NUMBER() OVER (ORDER BY C.C COLLATE SQL_Latin1_General_CP1_CS_AS) AS CSOrder,
ROW_NUMBER() OVER (ORDER BY C.C COLLATE SQL_Latin1_General_CP1_CI_AI) AS CIOrder,
ROW_NUMBER() OVER (ORDER BY C.C COLLATE Latin1_General_100_BIN) AS BINOrder
FROM C
ORDER BY CSOrder;
Notice that the sort order for the case sensitive and BIN collations is completely different, but the Case sensitive and Case Insentive collations share the same order:
Character CSOrder CIOrder BINOrder
--------- -------------------- -------------------- --------------------
A 1 1 1
a 2 2 27
B 3 3 2
b 4 4 28
C 5 5 3
c 6 6 29
D 7 7 4
d 8 8 30
E 9 9 5
e 10 10 31
F 11 11 6
f 12 12 32
G 13 13 7
g 14 14 33
H 15 15 8
h 16 16 34
I 17 17 9
i 18 18 35
J 19 19 10
j 20 20 36
K 21 21 11
k 22 22 37
L 23 23 12
l 24 24 38
M 25 25 13
m 26 26 39
N 27 27 14
n 28 28 40
O 29 29 15
o 30 30 41
P 31 31 16
p 32 32 42
Q 33 33 17
q 34 34 43
R 35 35 18
r 36 36 44
S 37 37 19
s 38 38 45
T 39 39 20
t 40 40 46
U 41 41 21
u 42 42 47
V 43 43 22
v 44 44 48
W 45 45 23
w 46 46 49
X 47 47 24
x 48 48 50
Y 49 49 25
y 50 50 51
Z 51 51 26
z 52 52 52
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 11, 2019 at 11:12 am
Thanks for your explanations
April 11, 2019 at 11:29 am
-- ben brugman 20190411
-- Same function not using a Collation instruction.
--
Declare @v varchar(300) = 'Ben Brugman'
Declare @x varchar(300) = 'BEN BRUGMAN'
--
IF CONVERT(varbinary, UPPER(@v)) = CONVERT(varbinary, @v) BEGIN PRINT 'UPPER' END ELSE PRINT 'lower'
IF CONVERT(varbinary, UPPER(@x)) = CONVERT(varbinary, @x) BEGIN PRINT 'UPPER' END ELSE PRINT 'lower'
April 15, 2019 at 4:56 am
Are you sure users will use only latin letters for their passwords?
Someone with the last name "Shilovich" must be well aware of other alphabets and code pages.
_____________
Code for TallyGenerator
April 15, 2019 at 11:25 am
Someone without a surname has to be a good fortuneteller to make predictions using a last name
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply