Monday, February 4, 2008

Today's Tip: Case-Sensitive Searching

Q: How do I search for case-sensitive characters in a case-insensitive database without altering the database?

A: 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'
From 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."

For the full discussion see this thread in the SQL Anywhere "General" newsgroup.

Add to Technorati Favorites

No comments: