Replacing strings in a database using Groovy

First my use case. A user inserted a couple of thousands records into a database describing animal samples for a transcriptomics study. In this table there is a column ‘description’ which is for free text. The user made a mistake: among other information in this description column he put ‘rat’ instead of ‘mouse’. So now, how to keep the text just replacing ‘rat’ by ‘mouse’? Groovy to our rescue!

def sql = groovy.sql.Sql.newInstance("jdbc:oracle:thin:@server.domain:1521:database", "user", "password", "driver")

sql.eachRow("select * from database.table where table_column_ = 'xxxx") {
newDescription = (it.description =~ /Rat/).replaceAll('Mouse')
id = it.id
updateCall = "update database.table set description = '$newDescription' where id = $id"
sql.executeUpdate(updateCall)
}

• Line 1 creates a sql connection to the database
• Line 3 iterates through the results of the passed select statement (each row will be in the it and fields can be accessed by their name)
• Line 4 does the regular expression find&replace in the description string
  • =~ looks if there is a match (takes it.description as argument)
  • The slash defines the RegEx. So in our case we are looking for Rat (/Rat/, for first letter lower and upper case we could have put /[R|r]at/)
  • and finally .replaceAll(‘Mouse’) to replace what was found with the regular expression
• Line 6 & 7 to build and call the update

Scripting languages like groovy can sometimes be very painful but luckily more often they are just great!