Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select query for IPAddress

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 use

select * from mytable where ID='12345' and IPAddress LIKE N'10.23.40.5%'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 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%'
Go to Top of Page

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) 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.
Go to Top of Page

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 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
Go to Top of Page

kevin2013
Starting Member

4 Posts

Posted - 2013-01-20 : 16:17:13
My IPAddress field is Varchar(15) types
my sample data is follows
ID IPAddress Location VisitTime
123 10.32020.11 USA 01-12-2013 05:20:23

I got this query and still does not return any rows

select * from mytable where ID ='123' and
IPAddress= CAST(N'10.32020.11' AS VARCHAR);
Go to Top of Page

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) 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!
Go to Top of Page

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) types
my sample data is follows
ID IPAddress Location VisitTime
123 10.32020.11 USA 01-12-2013 05:20:23

I got this query and still does not return any rows

select * from mytable where ID ='123' and
IPAddress= CAST(N'10.32020.11' AS VARCHAR);


what does this return?
select *,LEN(IPAddress) AS length from mytable where ID ='123'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -