SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select query for IPAddress
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kevin2013
Starting Member

4 Posts

Posted - 01/20/2013 :  00:41:43  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/20/2013 :  01:31:07  Show Profile  Reply with Quote
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/

Go to Top of Page

kevin2013
Starting Member

4 Posts

Posted - 01/20/2013 :  02:16:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/20/2013 :  06:08:27  Show Profile  Reply with Quote
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 - 01/20/2013 :  15:54:57  Show Profile  Reply with Quote
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 - 01/20/2013 :  16:17:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/20/2013 :  18:53:02  Show Profile  Reply with Quote
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!

Edited by - James K on 01/20/2013 18:53:41
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/21/2013 :  00:52:12  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000