Select query problem

Question

Hi.

I want to a simple select query from Mssql db but its getting error. I wanna search this;
-------------------------------------
"Cisco IOS Software, s4444_rp Software (s4444_rp-ADVIPSERVICESK9-M), Version , RELEASE SOFTWARE (fc6) Technical Support: http://www.cisco.com/techsupport Copyright (c) 1986-2008 by Cisco Systems, Inc. Compiled Wed 16-Jan-09 12:12 by prod_rel"
-------------------------------------

So I am using this query;
-------------------------------------
SELECT * FROM Servers where SysDescr='Cisco IOS Software, s6523_rp Software (s6523_rp-ADVIPSERVICESK9-M), Version 12.2(33)SXH1, RELEASE SOFTWARE (fc3) Technical Support: http://www.cisco.com/techsupport Copyright (c) 1986-2008 by Cisco Systems, Inc. Compiled Wed 16-Jan-08 23:50 by prod_rel'
-------------------------------------

But its getting this error;
-------------------------------------
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.
-------------------------------------

How may I search this?

Vulpes · Answer

Sorry yokzu I misread Mssql in your first post as Mysql. Unfortunately SQL Server doesn't support the STRCMP function. However you could try casting the text column to varchar and then using the = operator: SELECT * FROM Servers where CAST(SysDescr AS varchar) = 'Cisco IOS Software s6523_rp Software (s6523_rp-ADVIPSERVICESK9-M) Version 12.2(33)SXH1 RELEASE SOFTWARE (fc3) Technical Support: http://www.cisco.com/techsupport Copyright (c) 1986-2008 by Cisco Systems Inc. Compiled Wed 16-Jan-08 23:50 by prod_rel'

Vulpes · Answer

Yes when comparing with a long string it would be easy to have a capitalization or spacing mismatch with what's in the database.

Pravin More · Answer

hi yokzu it means query is correct as it it giving result when you match other string.... you might be comparing unmatching string with column....

yokzu · Answer

Hi Vulpes. I tryed your query but its getting no rows. Column's ( SysDescr) data type is text.

yokzu · Answer

Hi Vulpes. I tryed your query and I am getting this error; Msg 195 Level 15 State 10 Line 1 'STRCMP' is not a recognized built-in function name.

Vulpes · Answer

I wonder whether the STRCMP function might work - it returns 0 if two strings are equal: SELECT * FROM Servers where STRCMP(SysDescr 'Cisco IOS Software s6523_rp Software (s6523_rp-ADVIPSERVICESK9-M) Version 12.2(33)SXH1 RELEASE SOFTWARE (fc3) Technical Support: http://www.cisco.com/techsupport Copyright (c) 1986-2008 by Cisco Systems Inc. Compiled Wed 16-Jan-08 23:50 by prod_rel') = 0

yokzu · Answer

Hi Praven. When I try like this its working. SELECT * FROM Servers where SysDescr LIKE '%Cisco IOS Software%' ---->ok SELECT * FROM Nodes where SysDescr LIKE '%Cisco IOS Software s6523_rp Software%' ----->ok SELECT * FROM Nodes where SysDescr LIKE '%Cisco IOS Software s6523_rp Software (s6523_rp-ADVIPSERVICESK9-M)%' ----->ok SELECT * FROM Servers where SysDescr LIKE '%Compiled Wed 16-Jan-08 23:50 by prod_rel%' ---->ok But when I try like below all of them are getting no row. I tryed again and again but the result is same. SELECT * FROM Nodes where SysDescr LIKE '%Cisco IOS Software s6523_rp Software (s6523_rp-ADVIPSERVICESK9-M) Version 12.2(33)SXH1 RELEASE SOFTWARE (fc3) Technical%' ----> NOT OK SELECT * FROM Nodes where SysDescr LIKE '%isco Systems Inc. Compiled Wed 16-Jan-08 23:50 by prod_rel%' ----> NOT OK SELECT * FROM Nodes where SysDescr LIKE '%Technical Support: http://www.cisco.com/techsupport Copyright%' ----> NOT OK

Pravin More · Answer

hi yokzu like is working at my side. you might be comparing unmatching string with column.... for testing purpose check below one........... SELECT * FROM Servers where SysDescr='%Cisco IOS Software%' thanks Pravin.

yokzu · Answer

Thanks for your reply Pravin. When I try your query its working with no error but getting no data. Im just c/p this sentence from a cell and run query. Did I miss somethings?

Pravin More · Answer

hi yokzu you cannot use equal operator (=) on text datatype column . so use 'like' instead......like below............ SELECT * FROM Servers where SysDescr like 'Cisco IOS Software s6523_rp Software (s6523_rp-ADVIPSERVICESK9-M) Version 12.2(33)SXH1 RELEASE SOFTWARE (fc3) Technical Support: http://www.cisco.com/techsupport Copyright (c) 1986-2008 by Cisco Systems Inc. Compiled Wed 16-Jan-08 23:50 by prod_rel' Thanks Pravin.