none
Using equal operator in transact-SQL for ntext datatype column

    Question

  • Hi,

    I've a problem with using equal operator in transact-SQL for ntext datatype column.

    (SQL Server 2000)

    I'm using the following SQL command text.

    use Northwind
    Select * from Categories
    where Description ='Seaweed and fish'

    If I use 'like' operator intead of '=' then the qurey retuns correct value.

    Any idea about this?

    Any help is appreciated.

    Regards,

    Julia

    Tuesday, March 21, 2006 2:43 AM

Answers

  • From books online, index entry Boolean expressions:

    "Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. The following table lists the Transact-SQL comparison operators."

    = (Equals)
    > (Greater Than)
    < (Less Than)
    >= (Greater Than or Equal To)
    <= (Less Than or Equal To)
    <> (Not Equal To)
    != (Not Equal To)
    !< (Not Less Than)
    !> (Not Greater Than)

    If your description are always (or even usually) under 8000 characters, you need to change to a varchar type.  Or you might want to implement two columns, one a smaller description, the other a text column that has long values.  Using the current column:

    You can use LIKE or PATINDEX, or cast the values to a varchar (perhaps in a computed column) if you need to do this frequently and not on a really large value:

    create table testText

     --demo only, always have a pkey
     textCol text
    )
    insert into testText
    values ('searchThis')

    select *
    from testText
    where textCol = 'searchThis'

    /*
    Msg 402, Level 16, State 1, Line 1
    The data types text and varchar are incompatible in the equal to operator.
    */
    select *
    from testText
    where patindex('searchThis',textCol) > 0

    select *
    from testText
    where textCol like 'searchThis'


    alter table testText
    add smallversion as cast(textCol as varchar(20)) PERSISTED --persisted 2005 only

    select *
    from testText
    where smallversion = 'searchThis'

    Wednesday, March 22, 2006 2:41 PM

All replies

  • From books online, index entry Boolean expressions:

    "Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. The following table lists the Transact-SQL comparison operators."

    = (Equals)
    > (Greater Than)
    < (Less Than)
    >= (Greater Than or Equal To)
    <= (Less Than or Equal To)
    <> (Not Equal To)
    != (Not Equal To)
    !< (Not Less Than)
    !> (Not Greater Than)

    If your description are always (or even usually) under 8000 characters, you need to change to a varchar type.  Or you might want to implement two columns, one a smaller description, the other a text column that has long values.  Using the current column:

    You can use LIKE or PATINDEX, or cast the values to a varchar (perhaps in a computed column) if you need to do this frequently and not on a really large value:

    create table testText

     --demo only, always have a pkey
     textCol text
    )
    insert into testText
    values ('searchThis')

    select *
    from testText
    where textCol = 'searchThis'

    /*
    Msg 402, Level 16, State 1, Line 1
    The data types text and varchar are incompatible in the equal to operator.
    */
    select *
    from testText
    where patindex('searchThis',textCol) > 0

    select *
    from testText
    where textCol like 'searchThis'


    alter table testText
    add smallversion as cast(textCol as varchar(20)) PERSISTED --persisted 2005 only

    select *
    from testText
    where smallversion = 'searchThis'

    Wednesday, March 22, 2006 2:41 PM
  •  Julia168 wrote:

    Hi,

    I've a problem with using equal operator in transact-SQL for ntext datatype column.

    (SQL Server 2000)

    I'm using the following SQL command text.

    use Northwind
    Select * from Categories
    where Description ='Seaweed and fish'

    If I use 'like' operator intead of '=' then the qurey retuns correct value.

    Any idea about this?

    Any help is appreciated.

    Regards,

    Julia

    Wednesday, October 08, 2008 6:19 PM
  • Thanks for this. Really helped a problem I've been having all day!
    Sunday, March 07, 2010 10:02 PM
  • Thanks  !!!! Muchas gracias !!
    Tuesday, August 31, 2010 11:16 PM
  • Hi Louis,

    I just wanted to point out that while LIKE is valid syntax, it may not always return the "correct value," a detail your answer doesn't highlight. If there are rows where [Description] is longer than 4000 characters (or 8000 characters if the type is [text]), it's not possible to check for equality with LIKE. 

    If precise equality testing for [text]/ntext values in SQL Server 2000 is a critical need for anyone out there, ask about it.

    Steve Kass
    http://www.stevekass.com

    Wednesday, September 01, 2010 12:52 AM