• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 758
  • Last Modified:

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?
0
thehaze
Asked:
thehaze
  • 3
  • 3
  • 2
  • +2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
With Replace function. You can do this test in QA:

select replace('10.192.148.2','.','')
0
 
thehazeAuthor Commented:
How would this fit into a DTS package where I have a table containing 1500 ip-addresses?

ID1     !  IPaddr         !   Land
------------------------------------------
   1     !  10.192.10.1  !   UK
   2     !  10.192.10.2  !   UK
   3     !  10.192.10.3  !   UK

New table
ID1               !  IPaddr         !   Land
---------------------------------------------------
10192101    !  10.192.10.1     !   UK
10192101    !  10.192.10.2     !   UK
10192101    !  10.192.10.3     !   UK
0
 
adwisemanCommented:
What are you trying to do with this IP address?  We might be able to help you with more information.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
peh803Commented:
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
 
Vitor MontalvãoMSSQL 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
 
peh803Commented:
@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
 
thehazeAuthor Commented:
I have a DTS which imports the contents of an Excel Spreadsheet.
The sheet contains 3 columns:
IPaddr, Land and Comment

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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How are you populating the table in the DTS?

Phil, no worries :)
0
 
peh803Commented:
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
 
BillAn1Commented:
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
 
BillAn1Commented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now