I sometimes bulk upload data in oracle. The problem is that I sometimes get an INSERT statemnt like this:
INSERT INTO ALL_USER_HOTSPOT_DETAILS (USR_LOGIN,USR_LASTNAME,USR_FIRSTNAME,USR_EMAIL,
PROPERTYNR)
VALUES ('SABRDAG','D'AGOS','SABRINA','sabrina_d'agos@sheraton.com',70)
I would like to run an awk or sed command that would take care of the single quotes in the INSERT statement.
So the above string should be
INSERT INTO ALL_USER_HOTSPOT_DETAILS (USR_LOGIN,USR_LASTNAME,USR_FIRSTNAME,USR_EMAIL,PROPERTYNR)
VALUES ('SABRDAG','D''AGOS','SABRINA','sabrina_d''agos@sheraton.com',70);
I have tried awk but couldnt make it work. Any help would be appreciated.
I came up with a Perl solution, but would like to see a more elegant sed/awk solution as well.
#!/usr/bin/perl
# Auswipe - 10 May 2002
# Auswipe sez "No Guarantees!"
# Auswipe also sez "Test and Double Test! Auswipe is not responsible if anything gets franked!"
# Convert tic into double tic for contractions
# and single tic in names
open(QUERY, "query.txt") || die "$!";
open(NEWQUERY, ">query2.txt") || die "$!";
while ($inputLine = <QUERY>) {
while ($inputLine =~ m/(\w+)'(\w+)/) {
my $fore = $1;
my $aft = $2;
$inputLine =~ s/$fore'$aft/$fore"$aft/g;
};
print NEWQUERY "$inputLine";
};
Sample usage:
FreeBSD:joeuser:/home/joeuser/sample $ ll
total 8
-rwxr-xr-x 1 joeuser joeuser 519 May 10 10:35 perltic
-rw-r--r-- 1 joeuser joeuser 168 May 10 10:31 query.txt
FreeBSD:joeuser:/home/joeuser/sample $ ./perltic
FreeBSD:joeuser:/home/joeuser/sample $ ll
total 10
-rwxr-xr-x 1 joeuser joeuser 519 May 10 10:35 perltic
-rw-r--r-- 1 joeuser joeuser 168 May 10 10:31 query.txt
-rw-r--r-- 1 joeuser joeuser 168 May 10 10:38 query2.txt
FreeBSD:joeuser:/home/joeuser/sample $ cat query2.txt
NSERT INTO ALL_USER_HOTSPOT_DETAILS (USR_LOGIN,USR_LASTNAME,USR_FIRSTNAME,USR_EMAIL,
PROPERTYNR)
VALUES ('SABRDAG','D"AGOS','SABRINA','sabrina_d"agos@sheraton.com',70)
FreeBSD:joeuser:/home/joeuser/sample $
I'm not sure that I understand exactly was is going on here. But I think that you want any single quote surrounded by letters to become two single quotes. If that is correct (and it does seem crazy), try this...
That's what I was doing wrong with my sed statement. I was attempting to reference the atoms that I pulled with $1 and $2 like I did in the Perl code. I needed to use \1 and \2. D'oh.
Replacing the single tic with two single tics isn't crazy at all. After the insert takes place, the two single tics are converted into a single tic. It's the same for MS SQL Server as well.
The Perl code above can be fixed by replacing double quote with two single quotes back to back, but the sed expression is much cleaner.