awk command for INSERT statement

nattynatty · #1 (**permalink**) 05-10-2002

Hi,

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.

Thanx

auswipe · #2 (**permalink**) 05-10-2002

Wow. That is a tough one.

I came up with a Perl solution, but would like to see a more elegant sed/awk solution as well.

Code:

#!/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:

Code:

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 $

Two stars. Joe Bob says "Check it out"

nattynatty · #3 (**permalink**) 05-10-2002

Hi Auswipe,

I havent yet tested your code but the quotes in D''AGOS is to be two single quotes and not one double quote. The sample usage has one double quotes.

Natty

Perderabo · #4 (**permalink**) 05-10-2002

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...

sed "s/$[a-zA-Z]$\'$[a-zA-Z]$/\1\'\'\2/g"

auswipe · #5 (**permalink**) 05-10-2002

Quote:

Originally posted by Perderabo
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...

sed "s/$[a-zA-Z]$\'$[a-zA-Z]$/\1\'\'\2/g"

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.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
(sed) parsing insert statement column that crosses multiple lines	jjordan	Shell Programming and Scripting	3	10-09-2007 12:23 AM
unix command to insert double quotes	berlin_germany	Shell Programming and Scripting	2	01-17-2007 01:07 PM
Script does not execute Insert Statement	Amruta Pitkar	Shell Programming and Scripting	4	08-24-2006 11:14 PM
sql insert command	abey	Shell Programming and Scripting	2	05-19-2006 11:56 AM
Insert TAB in echo statement	sunils27	Shell Programming and Scripting	5	08-26-2005 03:36 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by