I missed the beginning of this thread but by looking at the last mail I
assume that you want to check the cases sensitivity of a string in Transact
SQL? Here is some code I worte to log a user into a site ensuring each
character of the submitted password is the correct case:
SELECT @endstring = ''
SELECT @passlen = LEN(@password)
SELECT @loop = 1
SELECT @startstring = "SELECT * FROM Login WHERE Username = '" + @username +
"' AND Password = '" + @password + "'"
WHILE @loop <= @passlen
BEGIN
SELECT @endstring = @endstring + " AND ascii(Substring(Password," +
CONVERT(char,@loop) + ",1)) = ascii(Substring('" + @Password + "'," +
CONVERT(char,@loop) + ",1))"
SELECT @loop = @loop + 1
END
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 419 4235
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: Paul Johnston [mailto:[EMAIL PROTECTED]]
Sent: 24 January 2001 13:03
To: CF-Talk
Subject: RE: case sensitivity in stored procedures
It can be done! You can bypass the case-insensitivity, although it's a bit
long winded (but it works).
What you need to do is use the ASCII character number to make a string case
sensitive (ie convert it into ASCII characters and test against those!).
there are two functions, ASCII and CHAR. Here's an example (from the
Transact-SQL Help) and note that D and d have different numbers:
SET TEXTSIZE 0
SET NOCOUNT ON
-- Create the variables for the current position of the character string
-- and for the character string.
DECLARE @position int, @string char(15)
-- Initialize the variables.
SET @position = 1
SET @string = 'Du monde entier'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
SET NOCOUNT OFF
GO
Here is the result set:
----------- -
68 D
----------- -
117 u
----------- -
32
----------- -
109 m
----------- -
111 o
----------- -
110 n
----------- -
100 d
----------- -
101 e
----------- -
32
----------- -
101 e
----------- -
110 n
----------- -
116 t
----------- -
105 i
----------- -
101 e
----------- -
114 r
Enjoy!
Paul
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 24, 2001 12:55 PM
> To: CF-Talk
> Subject: RE: case sensitivity in stored procedures
>
>
>
> Nick, this was helpful, thanks - I didn't realize it was an installation
> option, and I've never seen it installed except with the
> default setting. I
> looked it up in BOL and it gives pretty good details. I'm sure
> I've been
> lazy enough with object names, etc. that I wouldn't want
> case-sensitivity
> ON - half my queries or code might fail. Well maybe not half, but some.
>
> I had been trying out some code with tests of exact
> (case-sensitive) words,
> such as "WHERE Password LIKE '[S][e][c][R][e][t]', but so far it seems
> that the case-sensitivity setting relates to this too. As a
> result, SEcret,
> seCRET, SeCrEt, etc. all come up as LIKE the above expression.
>
> So it might be better to say that SQL Server is case-insensitive by
> default, period.
>
>
> Mark
> [EMAIL PROTECTED]
>
>
>
> Sebastian
>
> Apologies, not a very helpful answer.
>
> What I meant was SQL Server is case-sensitive, period.
>
> But actually that's not always true, only if SQL Server is
> installed with a case sensitive sort order.
>
> http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/
sql/8_ar_da_
10.htm
Nick
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
