>Is there a way to search for case-sensitive characters on a
>case-insensitive database without altering the database?
If you are not worried AT ALL about character-set, language or locale
issues, then doing a binary string comparison might be what you want.
BEGIN
DECLARE @x VARCHAR ( 100 );
DECLARE @y VARCHAR ( 100 );
SET @x = 'hello, world!';
SET @y = 'HeLlO, wOrLd!';
SELECT @x,
@y,
IF @x = @y
THEN 'Equal' ELSE 'Not equal' ENDIF,
IF CAST ( @x AS BINARY ) = CAST ( @y AS BINARY )
THEN 'Equal' ELSE 'Not equal' ENDIF;
END;
@x,@y,insensitive,sensitive
'hello, world!','HeLlO, wOrLd!','Equal','Not equal'
Breck
--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck....@risingroad.com
e.g. SELECT Compare('Hello', 'HELLO', 'UCA(case=respect;accent=ignore)');
returns 1 (i.e. first string is greater than the second).
Note: The above UCA-option will only run on SA 10.0.1 but older ASA versions
have other possible collation options like 'nocase'.
HTH
Volker
"Breck Carter [Team iAnywhere]" <NOSPAM_...@risingroad.com> wrote in
news:ja6mi3d2n7h4vgc4q...@4ax.com...
And if all else fails, loop through the string and use the ASCII function
which gives different results for uppercase and lowercase characters.
Frank
BEGIN
DECLARE @x VARCHAR ( 100 );
DECLARE @y VARCHAR ( 100 );
SET @x = 'hello, world!';
SET @y = 'HeLlO, wOrLd!';
SELECT @x,
@y,
IF @x = @y
THEN 'Equal' ELSE 'Not equal' ENDIF,
IF CAST ( @x AS BINARY ) = CAST ( @y AS BINARY )
THEN 'Equal' ELSE 'Not equal' ENDIF;
if 'Equal' then
set a=1;
else
set b=1;
end if;
END;
BEGIN
DECLARE @x VARCHAR ( 100 );
DECLARE @y VARCHAR ( 100 );
SET @x = 'hello, world!';
SET @y = 'HeLlO, wOrLd!';
IF CAST ( @x AS BINARY ) = CAST ( @y AS BINARY ) THEN
MESSAGE 'Equal' TO CLIENT;
ELSE
MESSAGE 'Not equal' TO CLIENT;
END IF;
END;
Or you can use store the result of an IF expression in a Y/N variable,
and use that variable in a later IF statement:
BEGIN
DECLARE @x VARCHAR ( 100 );
DECLARE @y VARCHAR ( 100 );
DECLARE @equal VARCHAR ( 1 );
SET @x = 'hello, world!';
SET @y = 'HeLlO, wOrLd!';
SET @equal = IF CAST ( @x AS BINARY ) = CAST ( @y AS BINARY )
THEN 'Y' ELSE 'N' ENDIF;
IF @equal = 'Y' THEN
MESSAGE 'Equal' TO CLIENT;
ELSE
MESSAGE 'Not equal' TO CLIENT;
END IF;
END;
The magic of IF and CASE expressions, as well as IF and CASE
statements, is all covered in my book.
Breck
--