Which database ?. This is for MySQL. Note user must have the correct permissions to create functions. The function is simply a demo, obviously you don't need a function to concat something to a field.

#!perl
use strict; 
use warnings; 
use DBI; 
use Data::Dump 'pp';

my $dbh = get_dbh();

my $proc = "
  CREATE FUNCTION my_subrt(s CHAR(30))
    RETURNS CHAR(50)
    RETURN CONCAT(s,'####');
  ";
$dbh->do('DROP FUNCTION IF EXISTS my_subrt'); 
$dbh->do($proc); 
#$dbh->do('DELETE FROM TABLE2'); 
my $sql = '
  INSERT INTO TABLE2 (COLUMN1, COLUMN2, COLUMN3)
  SELECT COLUMN1, COLUMN2, my_subrt(COLUMN3)
  FROM TABLE1 
  WHERE COLUMN4=?';
my $rv  = $dbh->do($sql, {}, 'foo');

my $ar = $dbh->selectall_arrayref('SELECT * FROM TABLE2');
pp $ar;

$dbh->do('DROP FUNCTION my_subrt'); 

sub get_dbh{
  my $database = "test"; my $user = ""; my $pw = "";
  my $dsn = "dbi:mysql:$database:localhost:3306"; 
  my $dbh = DBI->connect($dsn, $user, $pw, { 
         RaiseError=>1, AutoCommit=>1 } );
  return $dbh;
}
[download]

@poj

Although I decided to split up the statement and process the insert separately - with values from the select, I will definitely be trying this out later on one of my other projects.

Thanks!

Depending on your volume of data, in the past I have used the bulk loader in MSSQL to insert many records at once. It does require the load file to be accessible by the server. One advantage is you can check your function is working as expected before changing the database.

#!perl
use strict;
use DBI;
my $dbh = dbh();
my $sql = 'SELECT COLUMN1,COLUMN2,COLUMN3
           FROM TABLE1
           WHERE COLUMN4 = ?';
my $sth = $dbh->prepare($sql);
$sth->execute('foo');

my $tmpfile = "c:\\temp\\public\\temp1.dat";
open TMP,'>',$tmpfile or die "$!";
while (my @f = $sth->fetchrow_array){
  $f[2] = subrt($f[2]);
  print TMP (join "\t",@f)."\n";
}
close TMP;  
        
my $rv = $dbh->do('DELETE FROM TABLE2');
print "$rv records deleted from TABLE2\n";

$rv = $dbh->do( "
  BULK INSERT TABLE2
  FROM '$tmpfile' 
  WITH ( FIELDTERMINATOR = '\t' )" );
    
print "$rv records insert into TABLE2";

sub subrt {
  reverse shift
}  
  
# connect
sub dbh {
  my $dsn = "DBI:ODBC:mssql";
  my $dbh = DBI->connect($dsn, 'sa', '',
            {RaiseError => 1, PrintError => 1}) 
            or die (Error connecting " $DBI::errstr");
}
[download]

poj

but I'm not sure how to go about it (without splitting the INSERT statement into two parts and handling them separately). ...What I have below isn't going to work but hopefully it should give you an clear picture of what I'd like to achieve

That sounds like a good plan , do it in a way that is possible, the most obvious way, nothing wrong with that

Also see String::Interpolate/String::Interpolate::RE

And Perl Idioms Explained - ${\$obj->method} and @{[sort @list]}

I agree with Anonymous Monk that there is nothing wrong with using two SQL statements in this situation. But what is the function of the subroutine to which you want to pass the value of COLUMN3 ? Could you use an existing SQL function in your SELECT query? Or could you build a stored routine for your DB?

Built-in SQL functions
Stored routines in MySQL

But what is the function of the subroutine to which you want to pass the value of COLUMN3 ? Could you use an existing SQL function in your SELECT query? Or could you build a stored routine for your DB?

It did not even cross my mind to attempt either using an existing SQL function or building a stored routine within my DB and using that.

I have written a Perl function that takes a string and substitutes out certain characters from that string (the characters I'm taking out belong to a hash that I can extend as and when I need to

I'm not sure whether I can use an existing SQL function to achieve this, but it seemed like a good idea to have that function coded in Perl - seeing as how great Perl is with RegExps and the other programmer likely to be maintaining the code knows Perl..

Thanks for the advice! Once I get a handle on creating stored procedures in MSSQL I'm sure I'll be looking for every excuse to use them.

You might be able to do simple interpolation using nothing but SQL inline with a few subqueries and some trivial math.

If you truley need a perl function to do the job, you'll have to do a select in one call. And then prep an insert data set for a second call.

If you truley need a perl function to do the job, you'll have to do a select in one call. And then prep an insert data set for a second call.

That is what I suspected, but I'm very new to Perl and still far from sure of what is and isn't possible.

Thanks again for the advice. I appreciate it.

"INSERT INTO TABLE2 (COLUMN1, COLUMN2, ". my_subrt(COLUMN3) .") \n".

In SQL, the column list only supports columns, nothing else. The column list is simply a complete or partial list of columns in the table, in arbitrary order.

Function can only affect data, in which case it would be in the select clause, which definitely supports functions.

The very-short (but definitive) answer is ... “no.”

And here’s why:   “[any ...] SQL server has no idea who-or-what its client is ... nor does it care.”   The server knows nothing about you ... therefore, nothing about your programming language (or even that you are using one ...) ... therefore, nothing about your “functions.”   It has no ability,whatsoever, to “call them.”

If you desire to “do something fancy,” then you must provide all of the code to do it.   You must issue a SELECT statement, and, for each row returned, create and issue appropriate-to-you INSERTs, probably within a transaction.

It has no ability,whatsoever, to “call them.”

whatsoever? That is not correct. It definitely can, it's just unlikely you'd want to. In general, the database can react with the OS, and ask it to execute a program and capture the output. This is easily done in an sp or trigger, and, depending on what is done, in the actual statement, via a function to call the OS.

While a little trickier, you can even have a file stored on disk and join it into the database as an external table. If that table were actually a perl program, or--sneakier--an interpreter, you would indeed be able to put a function directly into the query.

That reply has hit the nail on the head.

I suspected as much but certainly didn't want to rule it out because I didn't know for sure if there was a clever way to intersperse/embed my function within the INSERT INTO/SELECT construct...

Thanks for setting me straight!

Even a broken clock is right once every 86400 seconds. But did you read and follow the other advice given in response to your question, where a number of experienced programmers explained how you can create a function in your SQL?

The way forward always starts with a minimal test.

if there was a clever way to intersperse/embed my function within the INSERT INTO/SELECT construct

That depends on what you want done and what language the function would be in. What exactly are you trying to do?