Select query problem

yokzu

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?

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted 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'
0
Vulpes14y ago
Yes, when comparing with a long string, it would be easy to have a capitalization or spacing mismatch with what's in the database.
0
Pravin More14y ago
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....
0
yokzu14y ago
Hi Vulpes. I tryed your query but its getting no rows. Column's (SysDescr) data type is "text".
0
yokzu14y ago
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."
0
Vulpes14y ago
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
0
yokzu14y ago
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
0
Pravin More14y ago
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.
0
yokzu14y ago
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?
0
Pravin More14y ago
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.
0