Search for partial text in text field

  • adonetok

    SSCrazy Eights

    Points: 8395

    There is a table Order in which one filed [Notes] as text data type.

    How to search a partial text such as "ordered on 10/02/2012" in the field [Notes]?

  • Lowell

    SSC Guru

    Points: 323463

    adonetok (4/25/2013)


    There is a table Order in which one filed [Notes] as text data type.

    How to search a partial text such as "ordered on 10/02/2012" in the field [Notes]?

    convert to varchar(max), and then you can use a LIKE statement...it will be slow,a nd involve a table scan, but that's the way:

    SELECT *

    FROM ORDERS

    WHERE CONVERT(varchar(max),[Notes])

    LIKE '%ordered on 10/02/2012%'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • DiverKas

    SSCrazy

    Points: 2049

    adonetok (4/25/2013)


    There is a table Order in which one filed [Notes] as text data type.

    How to search a partial text such as "ordered on 10/02/2012" in the field [Notes]?

    If this field is going to be searched often, I would strongly consider using Full Text Search (FTS) which is usually far better solution than using a non sargeble function and a LIKE with both sides wildcarded in terms of server cost.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply