CODEWITHSUNDEEP
sqlitesanitize
kallakafar
kallakafar

Reputation: 735

how to sanitize data in sqlite3 column

I have a sqlite3 table with a column by the name Title, which stores the names of the some movies. 

Table name - table1
Column name - Title
Examples data: "Casablanca" (1983) {The Cashier and the Belly Dancer (#1.4)}

I have another sqlite3 table with a column that stores movie titles. 

Table name - table2
Column name - Title
Examples data: casa blanca

Both these tables were created using different datasets, and as such although the movie name is the same (casa blanca vs "Casablanca" (1983) {The Cashier and the Belly Dancer (#1.4)}), both are stored with extra text.

What i would like to do is to SANITIZE the already stored data in both the columns. By sanitization, I would like to strip the cell content of: 1. spaces 2. spl chars like !, ', ", comma, etc.. 3. convert all to lower case

I hope with that atleast some level of matching can be had between both the columns.

My question is, how do i perform these sanitizations on data that is already stored in sqlite tables. I do not have an option to sanitize before loading, as i only have access to the loaded database.

I am using sqlite 3.7.13, and i am using sqlite manager as the gui.

Thank You.

Upvotes: 0

Views: 1474

Answers (1)

mvp
mvp

Reputation: 116078

This task is too specialized to be done in SQL only.

You should write simple Perl or Python script which will scan your table, read data row by row, scrub it to meet your requirements and write it back.

This is example in Perl:

use DBI;
my $dbh = DBI->connect("dbi:mysql:database=my.db");
# replace rowid with your primary key, but it should work as is:
my $sth = $dbh->prepare(qq{
    SELECT rowid,*
    FROM table1
});
while (my $row = $sth->fetchrow_hashref()) {
    my $rowid = $row->{rowid};
    my $title = $row->{title};
    # sanitize title:
    $title = lc($title); # convert to lowercase
    $title =~ s/,//g;    # remove commas
    # do more sanitization as you wish
    # ...
    # write it back to database:
    $dbh->do(
         qq{
             UPDATE table1
             SET title = ?
             WHERE rowid = ?
         }, undef,
         $title,
         $rowid,
    );
}
$sth->finish();
$dbh->disconnect();

Upvotes: 2

Related Questions