How do I compare nText fields

Posted on 2004-11-23
Last Modified: 2008-01-09
When I update a certain table I add records to an audit table that show any changes made to the fields. I have an nText field that I need to compare the original value against the new parameter that will be used to update the field. How can I do this?
Question by:Bryan_123
    LVL 16

    Assisted Solution

    You can only compare ntext values using LIKE not =. Is this enough to get you unstuck?
    LVL 50

    Expert Comment

    is it truely a TEXT column or is the size limited to something reasonable like 64K?

    if its small then

    you can convert the chunks to varchar(8000) pieces and compare those,
    initially do a Datalength(Old) = Datalength(New) test,.,,

    Author Comment

    Will that tell me if the value has changed?

    Author Comment

    It's an nText column that I expect will be huge.
    LVL 50

    Accepted Solution


    then use the BINARY_CHECKSUM or CHECKSUM function...


    Where  Datalength(OLD) <> DataLength(NEW)
     or Binary_Checksum(OLD) <> Binary_Checksum(NEW)

    the "checksum" functions compute a hash value based on the data within the column
    so its extremely unlikely that a change in the column data would generate the same hash...

    see BOL , Functions for more details


    Author Comment

    Thank you very much. I used a combination of both of your answers.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now