Author |
Topic |
kevin2013
Starting Member
4 Posts |
Posted - 2013-01-20 : 00:41:43
|
Guys, I have SQL table with Following field ID - int PK( NOT NULL) IPAddress - Nvarchar PK( NOT NULL) Location -Varchar Null UserName - varchr Null Data Looks like : 12345 10.23.40.5 OR jsmith 23244 130.23.34.5 FL Jenna i have two Primary Key: ID and IPAddress i need help on query as select * from mytable where ID='12345' and IPAddress='10.23.40.5' Problem : it does not return any rows. How to deal with this IPAddress field ? Pls Help |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-20 : 01:31:07
|
[code]select * from mytable where ID='12345' and IPAddress=N'10.23.40.5'or if it has any other unprintable characters present useselect * from mytable where ID='12345' and IPAddress LIKE N'10.23.40.5%'[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kevin2013
Starting Member
4 Posts |
Posted - 2013-01-20 : 02:16:51
|
quote: Originally posted by visakh16
select * from mytable where ID='12345' and IPAddress=N'10.23.40.5'or if it has any other unprintable characters present useselect * from mytable where ID='12345' and IPAddress LIKE N'10.23.40.5%' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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%' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-20 : 06:08:27
|
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) ExpectedValueFROM 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. |
|
|
kevin2013
Starting Member
4 Posts |
Posted - 2013-01-20 : 15:54:57
|
Last two column are same.but i need to select my query based on two valuesselect * 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 |
|
|
kevin2013
Starting Member
4 Posts |
Posted - 2013-01-20 : 16:17:13
|
My IPAddress field is Varchar(15) typesmy sample data is followsID IPAddress Location VisitTime 123 10.32020.11 USA 01-12-2013 05:20:23I got this query and still does not return any rowsselect * from mytable where ID ='123' andIPAddress= CAST(N'10.32020.11' AS VARCHAR); |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-20 : 18:53:02
|
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) ExpectedValueFROM 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! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-21 : 00:52:12
|
quote: Originally posted by kevin2013 My IPAddress field is Varchar(15) typesmy sample data is followsID IPAddress Location VisitTime 123 10.32020.11 USA 01-12-2013 05:20:23I got this query and still does not return any rowsselect * from mytable where ID ='123' andIPAddress= CAST(N'10.32020.11' AS VARCHAR);
what does this return?select *,LEN(IPAddress) AS length from mytable where ID ='123'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|