case-sensitive

[ASA 8.0]

Is there a way to search for case-sensitive characters on a
case-insensitive database without altering the database?

[Breck Carter [Team iAnywhere]]

On 1 Nov 2007 12:48:14 -0700, trenttc (ASA 8.0) wrote:

>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

[Volker Barth]

If you want more than just a binary comparison, have a look at the COMPARE()
function.
It can be used to do comparisons with different collations and the like.

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...

[Frank Ploessel]

Hi,

And if all else fails, loop through the string and use the ASCII function
which gives different results for uppercase and lowercase characters.

Frank

[trenttc]

I can use this logic because I don't have to worry about
collations or locale issues. How do use the results in the
same procedure, in an if then else?

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;

[Breck Carter [Team iAnywhere]]

You can do a direct comparison in an IF statement

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

--