IP address retrieval
-
Hi All,
I stored IP address into a field in a table in sql2k. The field is varchar (20), and the data is without dot.
Ex: 1821682521
When I query the table directly:
select IP from mytable where ip = '1821682521'
it returns data for me. But if I link the table with another table which also has IP field, it returns nothing for me even thoughthe other table has exact the same IP:
select a.ip, b.ip
from mytable a, mytable2 b
where a.ip = b.ip
Does anyone know why is that?
Thanks in advance for any inputs.
Minh Vu
-
What do you get when you run the following?
SELECT '|'+IP+'|', 'A' AS Ind FROM MyTable WHERE IP = '1821682521'
UNION ALL
SELECT '|'+IP+'|', 'B' AS Ind FROM MyTable2 WHERE IP = '1821682521'
Also, why did you you remove the periods without adding leading zeros to each octet?
You've made it possible to have a dupe that shouldn't be counted as a dupe 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Actually IP address is 4-byte value.
And it must be stored in BINARY(4) column.
Nothing to do with any kind of varchar here.
As soon as you've fixed you table design you not gonna have problems with queries.
_____________
Code for TallyGenerator -
As Jeff and Sergiy have identified, your data type is doinked. you should have either kept the full IP address, or converted it to an int or big int.
how do you know which value this is:
'1821682521' = 182.168.25.21 or 182.168.252.1
you can store an ip as an int, but a great article from david pool suggests putting it in 4 fields as small ints:
storing IP's in SQL Server:http://www.sqlservercentral.com/columnists/dpoole/2871.asp
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
With the PARSENAME function, there's no need even for that. PARSENAME (see Books Online) was developed almost soley for the purpose of interogating IP Addresses provided that the dots are left intact.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
damn I learned something new again. thanks Jeff;
Here's some crappy sample code; you'd probably want to convert to int in order to store the data:
declare @IP varchar(20)
SET @IP = '192.168.1.65'
select
PARSENAME(@IP,4) AS IP1,
PARSENAME(@IP,3) AS IP2,
PARSENAME(@IP,2) AS IP3,
PARSENAME(@IP,1) AS IP4
results:
IP1 IP2 IP3 IP4 192 168 1 65 Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for the feedback, Lowell
One more thing... you said the article explains how to save each "field" as SMALLINT. Even that is not necessary because each "field" of an IP address can only go from 0 to 255... that means TINYINT will work just fine (each is a single byte like Serqiy said).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That can also be used as a split function (when the dba doesn't want to have one on the server). Of course, that works only if you only need to have 4 items or less sent in the string (and you can use replace to change the delemiter from comma (or whatever it is) to a period for the function to work.
-
Actually, PARSENAME was not designed for parsing IP Addresses, it just happens to work, From BOL:
Returns the specified part of an object name. Parts of an object that can be retrieved are the object name, owner name, database name, and server name.
Just to keep the record straight.
-
Yes, not specifically for IP addresses, I use it to split up the text version of a performance counter name.
Anyway back on topic, did we solve the OP's problem
--------------------
Colt 45 - the original point and click interface
-
Yeah... Sorry about that... was thinking about something different and didn't double check.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply