question about like

Hello, i'm just wondering if i have for example a table with 2 colummns the first one is some primary key (int) and the other is of the image data type.
For example one row would be:

key data
232 0x7A5B6789D4E2...(thats just how sql server 2005 express shows me the data when i make a query insede Management Studio Express)

now i'm wondering if it is possible to do query for lets say %89D4% with LIKE,(i kinda want to search for a certain pattern of bits or in this case hexadecimal values ) i've tryed but i get an error. I just want to know if it is possible to do this with like and how and if it isn't, what other options do i have (full-text search - ?)?Well, the problem is that you are looking at binary data, not string data... Also, know that the image datatype is deprecated and should be replaced with varbinary(max).

What was the error you received?|||i tryed the following sql statement:
select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x%89D4% ;

and got the error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D4'.

however this statement: select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x89D4 ;
does not give me any errors, but this statement doesn't really help any, since it searches for the exact value 0x89D4.

and about the part that image is depricated, well the database that i have to use already exists, and is full of data so i'm not to sure that i have anything to say about the data types that they use, just have to go with what they have ...|||

FitzFarseer wrote:

i tryed the following sql statement:
select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x%89D4% ;

and got the error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D4'.

however this statement: select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x89D4 ;
does not give me any errors, but this statement doesn't really help any, since it searches for the exact value 0x89D4.

and about the part that image is depricated, well the database that i have to use already exists, and is full of data so i'm not to sure that i have anything to say about the data types that they use, just have to go with what they have ...

You need to surround your like statements with quotes...

select * from table where field like '%xxx%'|||select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE '%89D4%' ;

gives me the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of like function.|||For your reading pleasure, and for reference:

MSDN Transact-SQL Reference for: LIKE

http://msdn2.microsoft.com/en-us/library/ms179859.aspx|||

FitzFarseer wrote:

select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE '%89D4%' ;

gives me the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of like function.

I think this is a result of trying to use like on a binary column. Syntactically, it is correct.sql