#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    2
    Rep Power
    0

    How to update multiple (or all) SQL DB rows via a regex


    I have tried extensive googling but not had much luck.

    Our database is a 2008 MS SQL SERVER

    I'd like to utilise perl to iterate through all rows in a table, and alter the content of a given column using a s/bla/bla/ substitution.

    e.g. to find any data which contains certain unwanted characters and alter those

    Is this possible?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    776
    Rep Power
    495
    I have never had to do something like that, but I guess that you'll have to select all the rows, run your regex and update the records where you'll have made a change.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    225
    Rep Power
    41
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by Laurent_R
    I have never had to do something like that, but I guess that you'll have to select all the rows, run your regex and update the records where you'll have made a change.
    I had an inkling that this may be the case.

    SQL Server does not support regular expressions. It is simple enough to connect to it, to query it and run update statements - but an update statement cannot contain a regular expression....so no possibility to do a sweeping single update statement. Each row will have to be tackled one at a time in some way.

    I could select all my data into a hash, storing a primary key as the hash keys and then the column I wish to modify as the hash data - then perform the regex s/original/modified/ on all the hash data, then use this modified hash to update the table, row by row.
  8. #5
  9. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,221
    Rep Power
    1809
    You don't have to select all the data into memory to perform the conversion. It's just a matter of looping over the rows as if you were processing a big file line by line:

    Code:
    use DBI;
    
    my $dbh = DBI->connect("dbi:Pg:dbname=ato",'','',{RaiseError => 1, PrintError => 0});
    
    my $select = $dbh->prepare('SELECT id, name FROM employees');
    my $update = $dbh->prepare('UPDATE employees SET name = ? WHERE id = ?');
    
    $select->execute;
    
    while (my ($id,$name) = $select->fetchrow_array) {	
    	$name = lc $name;
    	$update->execute($name,$id);
    }

IMN logo majestic logo threadwatch logo seochat tools logo