Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Apply a function to each row in a table

Hello

I have a function that covnerts IPv6 into IPv4 and I need it to loop through each row in the table to perform the conversion.

The pertinent columns in the proxy table are

[ClientIP] -- this contains the IPv6 entries
[IPv4] -- I added this column in order to host the converted values.

What is the best method to accomplish this? Please note that my TSQL programming is only of a fundamental level!

I have attached the function and I can retrieve an output for single values such as:

SELECT [proxy].[dbo].[fnIpAddressToText] (
   'AC130C0A-FFFF-0000-0000-000000000000')

this obviously only accomplishes one conversion and I need it to loop through all the entries in a relatively large table (hundreds of thousands of rows).

We are using SQL Server 2008R2.

All help appreciated!
0
Jay Carax
Asked:
Jay Carax
1 Solution
 
PortletPaulCommented:
"I have attached the function"

I don't see that attachment
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Didn't know you can convert IPv6 to IPv4, since you will have much more addresses in IPv6 than IPv4.
I'm curious about how you going to do this (how to handle those not convertible addresses).
0
 
Jay CaraxAuthor Commented:
Function is now attached.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Jay CaraxAuthor Commented:
And Vitor, I've just had a word with the networks guys - it converts a hexedecimal value into IPv4 - apologies for the confusion. The attachment should clear things up.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Still can't see the attached function but saw the example you posted and by that you can't accomplish what you want, since IPv4 permits at maximum a value of 255 and for example FFFF it's 65535.
0
 
Koen Van WielinkIT ConsultantCommented:
Since you have the existing value as well as the conversion column in the same table, and a separate function to perform the conversion, this should be fairly straightforward:

update yourTable
set IP4V = [proxy].[dbo].[fnIpAddressToText] (ClientIP)

Open in new window


assuming that [proxy].[dbo].[fnIpAddressToText]  is your conversion function.
0
 
Jay CaraxAuthor Commented:
Exactly what was needed - thank you.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now