# DTS and string manipulation

In an SQL-table I have a nvarchar field 'IPadr' which typicall contains IP-addresse data like:

10.192.148.2

I need to convert this into a number which can be used as a unique key.

10.192.148.2 >>> 101921482

How do I do this within a DTS package?
###### Who is Participating?

Commented:
sorry, of course I forgot to point out that you can of course convert an IP address to an integer, as long as you convert the hexidecimal to  a signed integer, rather than an unsigned integer, and since you only want to ensure that the numebr is unique, you presumably do not have a problem if some of these numbers are negative?
as in

create function ip2int (@ip varchar(15))
returns integer
as
begin
declare @d1 integer
declare @d2 integer
declare @d3 integer
declare @ipint bigint
declare @ipbin binary(4)
set @d1 = charindex('.',@ip)
set @d2 = charindex('.',@ip,@d1 + 1)
set @d3 = charindex('.',@ip,@d2 + 1)
set @ipint = 0
if @d3 > 0
begin
set @ipbin = cast(cast(substring(@ip,1,@d1-1) as integer) as binary(1))
+ cast(cast(substring(@ip,@d1+1,@d2-@d1-1)as integer) as binary(1))
+ cast(cast(substring(@ip,@d2+1,@d3-@d2-1)as integer) as binary(1))
+ cast(cast(right(@ip,len(@ip) - @d3)as integer) as binary(1))
set @ipint = cast(@ipbin as integer)
end
return @ipint
end
0

MSSQL Senior EngineerCommented:
With Replace function. You can do this test in QA:

select replace('10.192.148.2','.','')
0

Author Commented:
How would this fit into a DTS package where I have a table containing 1500 ip-addresses?

------------------------------------------
1     !  10.192.10.1  !   UK
2     !  10.192.10.2  !   UK
3     !  10.192.10.3  !   UK

New table
---------------------------------------------------
10192101    !  10.192.10.1     !   UK
10192101    !  10.192.10.2     !   UK
10192101    !  10.192.10.3     !   UK
0

Commented:
0

Commented:
As far as I can see it, in DTS, you'd set your source equal to a query statement something like this:

SELECT replace('10.192.148.2','.','') AS ID1, IPaddr, Land FROM SourceTableName

Then, just map the ID1, IPaddr and Land fields to your destination table....

That should do the trick!

Regards,
Phil / peh803
0

MSSQL Senior EngineerCommented:
You need to change your DTS. There's two options:
1) Instead of passing all table, use a query to pass data and in that query use the Replace function
2) Add an OnSuccess flow to execute an SQL Script to update the ID1 with IPaddr with Replace function
0

Commented:
@adwiseman : please note that I've only taken @VMontalvao's query and put it in a very simple statement -- I was in no way trying to steal points.  Just wanted to make that clear in case anyone thought otherwise.

Regards,
Phil / peh803
0

Author Commented:
I have a DTS which imports the contents of an Excel Spreadsheet.
The sheet contains 3 columns:

The DTS
Creates a table structure containing IPaddr, Land and Comment.
Populates the table with the data.

I want to use the IPaddr as a unique key. I cannot use it In its present form of 10.192.10.3, so I need to convert it without the dots into a new unique key field. As shown in the previous tables.

0

MSSQL Senior EngineerCommented:
How are you populating the table in the DTS?

Phil, no worries :)
0

Commented:
One method I have used when dealing with the limitations of excel is a 2-step approach.

1.) import the excel data into SQL Server "import" in your database
2.) write a stored procedure to do your heavy lifting and call that once the data has been imported from excel

So in your case, you'd have something like this:
-- import data from excel to a table called "excel_import" or whatever you would want to call it
-- then, your stored procedure would do the select statement above + the insert

Your DTS package would run the import and then simply call the sproc.

This may be overkill in this case, but I think you'd definitely be able to get it working using this approach.

Regards,
Phil / peh803
0

Commented:
removing the '.' from an IP address string will not result in a unique key. e.g. the 2 IP addresses 10.192.10.3 and 101.92.10.3 will give the same number, of 101921013. Instead you need to convert properly to the corresponding numeric value by multiplying by 256, 256^2 etc.
However there are too many IP addresses to fit into an integer data type - IP addresses cover the range from 0 to 255.255.255.255 which is 0 to 4,294,967,295, whereas the largest int you can have is 2,147,483,647 therefore you need to use another datatype, e.g. bigint to store a uniqie number representing an IP address.
The following function converts an IP string to the corresponding bigint.
create function ip2bigint (@ip varchar(15))
returns bigint
as
begin
declare @d1 integer
declare @d2 integer
declare @d3 integer
declare @ipint bigint
set @d1 = charindex('.',@ip)
set @d2 = charindex('.',@ip,@d1 + 1)
set @d3 = charindex('.',@ip,@d2 + 1)
set @ipint = 0
if @d3 > 0
begin
set @ipint = ((cast(substring(@ip,1,@d1-1) * 256 as bigint)  + substring(@ip,@d1+1,@d2-@d1-1)) * 256 +  substring(@ip,@d2+1,@d3-@d2-1))*256 + right(@ip,len(@ip) - @d3)
end
return @ipint
end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.