SQL Server Performance

Testing Text Fields and Variables for Equality

Discussion in 'General Developer Questions' started by MartinSmithh, Oct 10, 2006.

  1. MartinSmithh New Member

    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?
  2. chiragkhabaria New Member

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

    Chirag

    http://chirikworld.blogspot.com/
  3. MartinSmithh New Member

    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)
  4. chiragkhabaria New Member

    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/
  5. MartinSmithh New Member

    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.
  6. chiragkhabaria New Member

    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/
  7. MartinSmithh New Member

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

  8. chiragkhabaria New Member

    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/
  9. Adriaan New Member

    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.
  10. MartinSmithh New Member

    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?
  11. MartinSmithh New Member

    Hi Adriaan,

    The other fields in the join are the tables PK.
  12. MartinSmithh New Member

    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?
  13. Adriaan New Member

    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.
  14. MartinSmithh New Member

    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?
  15. MartinSmithh New Member

    I think it's the ? mark characters.

    I posted the shortest bit of content with the problem. Others are a lot longer.
  16. Adriaan New Member

    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?
  17. MartinSmithh New Member

    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)
  18. Adriaan New Member

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

    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...
  20. Adriaan New Member

    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'.
  21. Adriaan New Member

    To complete the picture:

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

    also returns 'not like' ...
  22. MartinSmithh New Member

    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 />
  23. Adriaan New Member

    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.
  24. MartinSmithh New Member

    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...
  25. MartinSmithh New Member

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

Share This Page