Solved

Apply a function to each row in a table

Posted on 2014-09-11
7
151 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 47

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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 47

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

832 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