select * from mytable where ID='12345' and IPAddress=N'10.23.40.5'
or if it has any other unprintable characters present use
select * from mytable where ID='12345' and IPAddress LIKE N'10.23.40.5%'
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
select * from mytable where ID='12345' and IPAddress=N'10.23.40.5'
or if it has any other unprintable characters present use
select * from mytable where ID='12345' and IPAddress LIKE N'10.23.40.5%'
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
when i query like: select * from mytable whereID='12345'; it returns the row but it dont return on : select * from mytable where ID='12345' and IPAddress LIKE N'10.23.40.5%'
It may be that you the data that you have in the IPAddress column has some hidden characters within the string itself (rather than at the end of the string). Run this query:
SELECT *,
CAST(IPAddress AS VARBINARY) AS ColumnValue,
CAST(N'10.23.40.5' AS VARBINARY) ExpectedValue
FROM mytable whereID = '12345';
Now compare the last two columns - ColumnValue and ExpectedValue. If they are different, that means you have something spurious in your data.
Last two column are same. but i need to select my query based on two values select * from my table where ID='12345' and IPAddress='120.12.20.2'; how can i modify above query to get as i needed. pls help me
Something about what you are saying doesn't seem consistent. If the IPAddress field is VARCHAR(15), then the query I posted earlier should have shown different values for ColumnValue and ExpectedValue. But you were getting the same value.
Can you run the following queryies and post the results?
SELECT DATA_TYPE FROM INFORMATION_SCHEMA.[COLUMNS]
WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME='IPAddress';
SELECT IPAddress,
CAST(IPAddress AS VARBINARY) AS ColumnValue,
CAST(N'10.23.40.5' AS VARBINARY) ExpectedValue
FROM mytable whereID = '12345';
I am really grasping at straws because I don't think varchar vs nvarchar should make any difference in this case. There is something very simple and very obvious that we are missing - but it is so obvious that I can't figure out what it might be!
Select query for IPAddress
Posted - 01/20/2013 : 00:41:43
