Select query with dots

Question

Hi I want to search an IP address from DB. Query is like below; select * FROM Log WHERE IP='3.3.3.3' But I get this error The data types ntext and varchar are incompatible in the equal to operator. IP colon's format is nText. How may I search IP addresses? regards.

Vulpes · Answer

Try this instead: select * FROM Log WHERE CAST(IP as nvarchar(max)) = '3.3.3.3' EDIT: Senthil beat me to it :)

yokzu · Answer

Thanks for your replys guys. It really help me.

Senthilkumar · Answer

Vulpes :-)

Senthilkumar · Answer

Hi If you defined a column as integer then you can directly search by the value. CREATE TABLE Example ( ID INT IDENTITY(11) PRIMARY KEY Name Varchar(10) } Here the Id is integer column and name is varchar. When you write the search ID then SELECT * FROM Example WHERE ID=2 If you want to search the exactly name then SELECT * FROM Example WHERE name='Senthil' If you want to search the similar kind of names then SELECT * FROM Example WHERE name LIKE '%Senthil%' --It will search the senthil which consist some thing like 'kumarsenthil' SELECT * FROM Example WHERE name LIKE '%Senthil%' --It will search by name starts Senthil like 'Senthilkumar''SenthilRaja' SELECT * FROM Example WHERE name LIKE 'Senthil%' -- It will search the name some thing like 'KumarSenthil' I come to your example Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text ntext or image data types. The following table lists the Transact-SQL comparison operators. = (Equals) > (Greater Than) >= (Greater Than or Equal To) (Not Equal To) != (Not Equal To) ! !> (Not Greater Than) So we need to make both columns are equal data type to evaluate the expression. SELECT * FROM Log WHERE CAST(IP AS nvarchar(4000))='3.3.3.3' This will help you to fix. If it is useful then mark it as Correct Answer

yokzu · Answer

Thanks for your repley vulpes I changes my query like below; SELECT * FROM Log WHERE IP=CAST('+TextBox3.Text.ToString()+' as ntext); But I get this error; The data types ntext and ntext are incompatible in the equal to operator.

Vulpes · Answer

Try: select * FROM Log WHERE IP = CAST('3.3.3.3' AS ntext)