Search for partial text in text field

Question

Search for partial text in text field

adonetok

SSCrazy Eights

Points: 8395
More actions
April 25, 2013 at 10:35 am

#273275

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]?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 More actions · Answer 2

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.