Testing Text Fields and Variables for Equality

How can I do this (SQL 2000).

I have a stored procedure that accepts a text parameter (@BodyText).

I only want to store this if it is different from what is stored already.

I tried using = and got the message saying this was invalid for text datatypes.

I then tried the following



IF EXISTS(SELECT
*
FROM tblContentVersions
WHERE
(FTIdentifier = @FTIdentifier) AND (VersionId = @VersionId)
AND (SUBSTRING(BodyText, 1, DATALENGTH(BodyText)) = SUBSTRING(@BodyText, 1, DATALENGTH(@BodyText))))
BEGIN
SET @IsContentAltered = 0
END
ELSE
BEGIN
SET @IsContentAltered = 1
END




But this seems to not catch some changes (I suspect very likely if the 2 are different after the 8000th character)

Any ideas?

make you of "Like" Instead of "=".

Chirag

http://chirikworld.blogspot.com/

LIKE doesn't test for equality

I tested this by doing


SELECT COUNT(*) AS Expr1
FROM tblContentVersions INNER JOIN
tblContentVersions tblContentVersions_1 ON tblContentVersions.FTIdentifier = tblContentVersions_1.FTIdentifier AND
tblContentVersions.VersionId = tblContentVersions_1.VersionId

(1738 records)

and



SELECT COUNT(*) AS Expr1
FROM tblContentVersions INNER JOIN
tblContentVersions tblContentVersions_1 ON tblContentVersions.FTIdentifier = tblContentVersions_1.FTIdentifier AND
tblContentVersions.VersionId = tblContentVersions_1.VersionId AND tblContentVersions.BodyText LIKE tblContentVersions_1.BodyText


(1607 records)

for the text datatype Like is only way to compare. or you have to convert to varchar and then use =

have a look at the following script



Declare @test table
(
i text
)

Insert @test
select '12312312fsdafadsfasf' Union all
select '2343243212312312fsdafadsfasf'

Declare @test1 table
(
i text
)

Insert @test1
select '12312312fsdafadsfasf' Union all
select '2343243212312312fsdafadsfasf'

Select * From @test a ,@test1 b where a.i like b.i


BTW both the queries are different, so thatz why you are getting differnt row count..


Chirag

http://chirikworld.blogspot.com/

What the queries are doing is

(A) Join a table onto itself using the Primary Key (returns all rows as expected)

(B) Join a table onto itself using the Primary Key and Like on the text field.


If Like is accurate in testing equality then both should return the same. They don't. (and none of the text values are null).

Therefore I conclude LIKE won't do what I want.

Converting to VARCHAR will obviously give me the same 8000 char limitation as before.

Compare the Column values .


SELECT tblContentVersions.BodyText, tblContentVersions_1.BodyText
FROM tblContentVersions INNER JOIN
tblContentVersions tblContentVersions_1 ON tblContentVersions.FTIdentifier = tblContentVersions_1.FTIdentifier AND
tblContentVersions.VersionId = tblContentVersions_1.VersionId AND tblContentVersions.BodyText Not LIKE tblContentVersions_1.BodyText

And then run this query

SELECT tblContentVersions.BodyText, tblContentVersions_1.BodyText
FROM tblContentVersions INNER JOIN
tblContentVersions tblContentVersions_1 ON tblContentVersions.FTIdentifier = tblContentVersions_1.FTIdentifier AND
tblContentVersions.VersionId = tblContentVersions_1.VersionId AND tblContentVersions.BodyText LIKE tblContentVersions_1.BodyText



Chirag

http://chirikworld.blogspot.com/

I'm not being funny but if it's a self join the values are clearly going to be the same.

yes, i am also not being funny, i am just asking you to run these to queries so that you will come to know, what made count differ .....

Chirag

http://chirikworld.blogspot.com/

The values will only be the same if the column on which you join is a unique key to the table. If not, then you might be matching different rows, where the column might contain different data.

Well I've run it and the ones returned by NOT LIKE look the same (hardly surprising as they are the same!)<br /><br />Here is an example of one of the fields returned as being NOT LIKE when joined to itself<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />&lt;object id="ieFlashMovie" codebase="http://fpdownload.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,0,0" height="145" width="300" classid="clsid<img src='/community/emoticons/emotion-2.gif' alt=':d' />27cdb6e-ae6d-11cf-96b8-444553540000"&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="GetAsset.ashx?AssetId=1951" name="movie" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="high" name="quality" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="show all" name="scale" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="#FFFFFF" name="bgcolor" /&gt;&lt;/object&gt;&lt;object id="flashMovie" type="application/x-shockwave-flash" height="145" width="300" data="GetAsset.ashx?AssetId=1951"&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="high" name="quality" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="show all" name="scale" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="#FFFFFF" name="bgcolor" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="http://www.macromedia.com/go/getflashplayer" name="pluginurl" /&gt;&lt;/object&gt;&lt;!--&gt; &lt;![endif]--&gt;<br /></font id="code"></pre id="code"><br /><br />I wonder if there are characters needing escaping or something?

Hi Adriaan,

The other fields in the join are the tables PK.

OK.

I think I see why LIKE isn't working.

The content may contain wildcards which are messing it up.

I could write a UDF to chop it into 8000 character blocks and compare all of them but I'm sure there must be a better way. Isn't there?

Don't bother with a UDF - the string you posted is about 750 characters long ...

I tried with your string, and yes: NOT LIKE thinks the same string is different. You would expect to find a wildcard character in there, but for the life of me I can't find any ...

The <> operator handles it correctly.

It looks as if to use LIKE successfully in this situation I need to use the ESCAPE keyword.

But then I'll need to use REPLACE to change all WildCards to escaped versions. Which will still mean chopping it up into VARCHAR segments.

Is there really no easier way to test text equality?

I think it's the ? mark characters.

I posted the shortest bit of content with the problem. Others are a lot longer.

As far as I know ? is not a wildcard in T-SQL (it is in Jet SQL).

I did a REPLACE() for '?' to '[?]' but with no success.

... NOT LIKE ... ESCAPE '?' --------- wrong!
... LIKE ... ESCAPE '?' --------- this one evaluates correctly!
NOT (... LIKE ... ESCAPE '?') --------- is wrong again!

What is the problem just using the <> operator?

Hi Adriann I can't use = or <> with the text datatype.

This data (despite the length of the example I posted) needs to be stored as text because it is often > 8000 characters)

Not sure if there could be a problem with the data being in XML format.

It's HTML rather than XML.

(It's from a Web content management system)

I believe in SQL 2005 this will all be a lot easier but currently we are stuck with SQL 2000...

Now for something wild:<br /><br />WHERE (a LIKE b) AND (a NOT LIKE b)<br /><br />As pointless as it may seem, it does work for your data - it does evaluate correctly. Here's a test script to reproduce the behaviour:<pre id="code"><font face="courier" size="2" id="code">declare @test varchar(1000)<br />SET @test = '&lt;object id="ieFlashMovie" codebase="http://fpdownload.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,0,0"<br />height="145" width="300" classid="clsid<img src='/community/emoticons/emotion-2.gif' alt=':d' />27cdb6e-ae6d-11cf-96b8-444553540000"&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="GetAsset.ashx?AssetId=1951" name="movie" /&gt;<br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="high" name="quality" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="show all" name="scale" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="#FFFFFF" name="bgcolor" /&gt;&lt;/object&gt;<br />&lt;object id="flashMovie" type="application/x-shockwave-flash" height="145" width="300" data="GetAsset.ashx?AssetId=1951"&gt; <br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="high" name="quality" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="show all" name="scale" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="#FFFFFF" name="bgcolor" /&gt; <br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="http://www.macromedia.com/go/getflashplayer" name="pluginurl" /&gt;&lt;/object&gt;&lt;!--&gt; &lt;![endif]--&gt;'</font id="code"></pre id="code"><pre id="code"><font face="courier" size="2" id="code">SELECT CASE WHEN (@test LIKE @test) AND (@test NOT LIKE @test) THEN 'not like' else 'like' end,<br />CASE WHEN (@test NOT LIKE @test) THEN 'not like' else 'like' end</font id="code"></pre id="code">The first expression returns 'like', the second expression returns 'not like'.

To complete the picture:

CASE WHEN (@test LIKE @test) THEN 'like' else 'not like' end

also returns 'not like' ...

I think you may have got a bit carried away there?! (please correct me if I've misunderstood)<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />declare @test varchar(1000)<br />declare @test2 varchar(1000)<br /><br />SET @test = '&lt;object id="ieFlashMovie" codebase="http://fpdownload.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,0,0"<br />height="145" width="300" classid="clsid<img src='/community/emoticons/emotion-2.gif' alt=':d' />27cdb6e-ae6d-11cf-96b8-444553540000"&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="GetAsset.ashx?AssetId=1951" name="movie" /&gt;<br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="high" name="quality" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="show all" name="scale" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="#FFFFFF" name="bgcolor" /&gt;&lt;/object&gt;<br />&lt;object id="flashMovie" type="application/x-shockwave-flash" height="145" width="300" data="GetAsset.ashx?AssetId=1951"&gt; <br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="high" name="quality" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="show all" name="scale" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="#FFFFFF" name="bgcolor" /&gt; <br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="http://www.macromedia.com/go/getflashplayer" name="pluginurl" /&gt;&lt;/object&gt;&lt;!--&gt; &lt;![endif]--&gt;'<br /><br />set @test2 = 'bob'<br /><br />SELECT CASE WHEN (@test LIKE @test2) AND (@test NOT LIKE @test2) THEN 'not like' else 'like' end,<br />CASE WHEN (@test NOT LIKE @test2) THEN 'not like' else 'like' end<br /></font id="code"></pre id="code"><br />

Getting carried away - probably yes, but hopefully not beyond the point where I can regain my senses ...

If you do

SET @test = <your html string>

then it doesn't matter if you do

SET @test2 = @test
or
SET @test2 = 'bob'

-- they both return like & not like

***

If you do

SET @test = 'bob'
SET @test2 = @test

--- it returns like & like.

It seems to be the html string content that is causing the weird behaviour, and for some weird reason (@test LIKE @test) AND (@test NOT LIKE @test) returns the correct evaluation ...

... FOR THIS VERY SPECIFIC DATA.

Thanks for all your research into this.

I'll have a bit of a play around with some real data to see if I can get this working for me. I'll post my results in a bit...

There's a good chance I've missed what you meant here but when I try the following<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br /><br />declare @test varchar(1000)<br /><br />declare @test2 varchar(1000)<br /><br /><br />SET @test = '&lt;object id="ieFlashMovie" codebase="http://fpdownload.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,0,0"height="145" width="300" classid="clsid<img src='/community/emoticons/emotion-2.gif' alt=':d' />27cdb6e-ae6d-11cf-96b8-444553540000"&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="GetAsset.ashx?AssetId=1951" name="movie" /&gt;&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="high" name="quality" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="show all" name="scale" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="#FFFFFF" name="bgcolor" /&gt;&lt;/object&gt;&lt;object id="flashMovie" type="application/x-shockwave-flash" height="145" width="300" data="GetAsset.ashx?AssetId=1951"&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="high" name="quality" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="show all" name="scale" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="#FFFFFF" name="bgcolor" /&gt; &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />aram value="http://www.macromedia.com/go/getflashplayer" name="pluginurl" /&gt;&lt;/object&gt;&lt;!--&gt; &lt;![endif]--&gt;'<br /><br /><br />set @test2 = 'obb'<br /><br /><br />SELECT CASE WHEN (@test LIKE @test2) AND (@test NOT LIKE @test2) THEN 'not like' else 'like' end,CASE WHEN (@test NOT LIKE @test2) THEN 'not like' else 'like' end, CASE WHEN (@test LIKE @test2) THEN 'like' else 'not like' end<br /><br /><br /><br />set @test2 = @test<br /><br /><br />SELECT CASE WHEN (@test LIKE @test2) AND (@test NOT LIKE @test2) THEN 'not like' else 'like' end,CASE WHEN (@test NOT LIKE @test2) THEN 'not like' else 'like' end, CASE WHEN (@test LIKE @test2) THEN 'like' else 'not like' end<br /><br /><br /><br /><br /><br /></font id="code"></pre id="code">.<br /> <br /><br />They both return "like, not like, not like" so I can't see how it will help me test if the 2 strings are equal<br /><br /><br />Have I made a mistake here?<br /><br />I might just use the following...<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />SET QUOTED_IDENTIFIER ON <br />GO<br />SET ANSI_NULLS ON <br />GO<br /><br /><br /><br /><br />CREATE FUNCTION IsTextEqual<br />(@Test1 text,<br /> @Test2 text)<br />RETURNS bit<br />AS<br />BEGIN<br /><br />/*<br />Returns<br /><br />Null if Comparing Nulls <br />1 If Both Are Equal<br />0 If Different<br /><br />*/<br /><br /><br />DECLARE @Result bit<br /><br />IF ((@Test1 IS NUll) OR (@Test2 IS NUll))<br />BEGIN<br />RETURN NULL<br />END<br /><br />IF (DATALENGTH(@Test1) &lt;&gt; DATALENGTH(@Test2))<br />BEGIN<br />RETURN 0<br />END<br /><br /><br />SET @Result = 1<br /><br />DECLARE @COUNTER INT<br />SET @COUNTER = 0<br /><br />WHILE ((@COUNTER * 8000) &lt; DATALENGTH(@Test1))<br />BEGIN<br /><br /><br />IF (SUBSTRING(@Test2, 1 + (@COUNTER * 8000), ((@COUNTER + 1) * 8000)) &lt;&gt; SUBSTRING(@Test1, 1 + (@COUNTER * 8000), ((@COUNTER + 1) * 8000)))<br />BEGIN<br />SET @Result = 0<br />BREAK <br />END<br />SET @COUNTER = @COUNTER + 1<br />END<br /><br /><br />RETURN @Result<br /><br />END<br /><br /><br /><br /><br />GO<br />SET QUOTED_IDENTIFIER OFF <br />GO<br />SET ANSI_NULLS ON <br />GO<br /><br /><br /></font id="code"></pre id="code">