Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Apply a function to each row in a table

Posted on 2014-09-11
7
152 Views
Last Modified: 2014-09-12
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
Comment
Question by:Jay Carax
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40316818
"I have attached the function"

I don't see that attachment
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40316829
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
 

Author Comment

by:Jay Carax
ID: 40316895
Function is now attached.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Jay Carax
ID: 40316905
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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40316926
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
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40316942
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
 

Author Closing Comment

by:Jay Carax
ID: 40319484
Exactly what was needed - thank you.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question