case sensitive testing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keith G Hicks - 30 Dec 2008 20:06 GMT

This is related to my earlier triggger question but it's a completely
different issue.

My db (sql 2005) is not set as case sensitive. However, I have a trigger
where I need to compare the old and new values of one particular column and
if it's been changed to mark it so. Only this one test in this procedure
needs to be case sensitive. Nothing else in the db or in this procedure
should be case sensitive.

I know this sounds odd but it's a legal thing. If some text was this way:

"John smith being duly sworn on this....."

changes to this:

"John Smith being duly sworn on this..."

then the client needs to know there was a change because the "S" in smith
was change to upper case.

Right now my code is like this:

IF (SELECT Notes FROM INSERTED) <> (SELECT Notes FROM DELETED)

For that one line I need case to be important. How could this be done? I'm
thinking that the only way to do it is to create a function that is case
sensitive and pass the values to that. So the trigger would have this in it
instead of the above:

IF dbo.CompareNotesValues(SELECT Notes FROM INSERTED, SELECT Notes FROM
DELETED)

Thanks,

Keith

Reply to this Message

Alex Kuznetsov - 30 Dec 2008 20:13 GMT

> This is related to my earlier triggger question but it's a completely
> different issue.
[quoted text clipped - 31 lines]
>
> Keith

have you tried casting your text to VARBINARY?

Reply to this Message

Keith G Hicks - 30 Dec 2008 20:41 GMT

No. I hadn't thought of that. Thank you. That should work.

On Dec 30, 2:06 pm, "Keith G Hicks" <k...@comcast.net> wrote:

> This is related to my earlier triggger question but it's a completely
> different issue.
[quoted text clipped - 33 lines]
>
> Keith

have you tried casting your text to VARBINARY?

Reply to this Message

Keith G Hicks - 30 Dec 2008 20:44 GMT

I spoke too soon. Apparently I cannot cast TEXT types to VARBINARY(MAX).
Now I'm stuck. Other ideas?

> No. I hadn't thought of that. Thank you. That should work.
>
[quoted text clipped - 39 lines]
>
> have you tried casting your text to VARBINARY?

Reply to this Message

Alex Kuznetsov - 30 Dec 2008 21:36 GMT

> I spoke too soon. Apparently I cannot cast TEXT types to VARBINARY(MAX).
> Now I'm stuck. Other ideas?
[quoted text clipped - 42 lines]
>
> > have you tried casting your text to VARBINARY?

CREATE TABLE #t(i INT, t TEXT);
GO
INSERT #t SELECT 1, 'Tank' UNION ALL SELECT 2, 'tank'
GO
-- values are different
SELECT i, CAST(CAST(t AS VARCHAR(MAX)) AS VARBINARY(MAX)) FROM #t

Reply to this Message

Keith G Hicks - 30 Dec 2008 21:40 GMT

Ok. Thanks. I sort of realized that in the post I just put up parallel to
yours (posted mine before I saw yours). I didnt' realize that double casting
like that would work. I figured sql wouldn't like that and would still tell
me I was trying to cast TEXT as VARBINARY. Thanks for the confirmation.

On Dec 30, 2:44 pm, "Keith G Hicks" <k...@comcast.net> wrote:

> I spoke too soon. Apparently I cannot cast TEXT types to VARBINARY(MAX).
> Now I'm stuck. Other ideas?
[quoted text clipped - 50 lines]
>
> > have you tried casting your text to VARBINARY?

CREATE TABLE #t(i INT, t TEXT);
GO
INSERT #t SELECT 1, 'Tank' UNION ALL SELECT 2, 'tank'
GO
-- values are different
SELECT i, CAST(CAST(t AS VARCHAR(MAX)) AS VARBINARY(MAX)) FROM #t

Reply to this Message

Keith G Hicks - 30 Dec 2008 21:41 GMT

This seems to work:

DECLARE
@OldNotes VARCHAR(MAX)
@NewNotes VARCHAR(MAX)

SET @OldNotes = (SELECT Notes From DELETED)
SET @NewNotes = (SELECT Notes From INSERTED)

IF CAST(@OldNotes AS VARBINARY(MAX)) <> CAST(@NewNotes AS VARBINARY(MAX))

"Notes" is a TEXT type. So I'm basically saving it into a VARCHAR(MAX)
variable and then casting that to VARBINARY(MAX).

Could this create problems that I'm not aware of?

>I spoke too soon. Apparently I cannot cast TEXT types to VARBINARY(MAX).
>Now I'm stuck. Other ideas?
[quoted text clipped - 44 lines]
>>
>> have you tried casting your text to VARBINARY?

Reply to this Message

Hugo Kornelis - 30 Dec 2008 21:52 GMT

>This is related to my earlier triggger question but it's a completely
>different issue.
[quoted text clipped - 19 lines]
>
>IF (SELECT Notes FROM INSERTED) <> (SELECT Notes FROM DELETED)

Hi Keith,

The first problem with this is that this will result in an error as soon
as a multi-row update is performed. You should rewrite your trigger to
handle mutliple rows in inserted and deleted. Something like this:

SELECT .....
FROM inserted AS i
INNER JOIN deleted AS d
ON d.PrimaryKey = i.PrimaryKey
WHERE d.Notes <> i.Notes;

Of course, this still doesn't solve the case sensitivity. You can solve
this by specifying a collation to use for the comparison. For instance,
if you want the comparison to be case sensitive but accent insensitive
and you want to use the Latin1-General collation, you would change the
last line to

WHERE d.Notes <> i.Notes COLLATE Latin1_General_CS_AI;

However, this will result in an error as well. You can't coompare text
columns using <> (or any other comaprison operator). Up to SQL Server
2000, you had to use special functions and lots of code. Since SQL
Server 2005, you can luckily simply cast to varchar(max) instead:

WHERE CAST(d.Notes AS varchar(max)) <>
CAST(i.Notes AS varchar(max)) COLLATE Latin1_General_CS_AI;

And this will indeed work. Just don't forget to doublecheck which
collation you actaullly need (the one I used was an example picked at
random).

Signature

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Reply to this Message

Alex Kuznetsov - 30 Dec 2008 22:09 GMT

On Dec 30, 3:52 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:

> >This is related to my earlier triggger question but it's a completely
> >different issue.
[quoted text clipped - 47 lines]
> WHERE CAST(d.Notes AS varchar(max)) <>
> CAST(i.Notes AS varchar(max)) COLLATE Latin1_General_CS_AI;

That's better than my suggestion. If the column is nullable, you just
need to add this:

OR (d.Notes IS NULL AND i.Notes IS NOT NULL) OR (i.Notes IS NULL AND
d.Notes IS NOT NULL)

Reply to this Message

Keith G Hicks - 31 Dec 2008 02:58 GMT

Thanks guys. That works great!

Keith

On Dec 30, 3:52 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:

> On Tue, 30 Dec 2008 15:06:54 -0500, Keith G Hicks wrote:
> >This is related to my earlier triggger question but it's a completely
[quoted text clipped - 49 lines]
> WHERE CAST(d.Notes AS varchar(max)) <>
> CAST(i.Notes AS varchar(max)) COLLATE Latin1_General_CS_AI;

That's better than my suggestion. If the column is nullable, you just
need to add this:

OR (d.Notes IS NULL AND i.Notes IS NOT NULL) OR (i.Notes IS NULL AND
d.Notes IS NOT NULL)

Reply to this Message