Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk command for INSERT statement

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk command for INSERT statement
# 1  
Old 05-10-2002
Data awk command for INSERT statement
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
# 2  
Old 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"
# 3  
Old 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
# 4  
Old 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"
# 5  
Old 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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Single quotes insert statement using awk

Hi, Need help, using awk command to insert statement awk -v q="'" '{ print "db2 connect to repolab > /dev/null; " "\n" "db2 -x \" select name from IBMPDQ.PROFILE where managed_database = " q $1"_"$3"__0" q "\"" } ' profile.txt | sh - | awk -v i="'" ' { print "db2 connect to repolab >... (1 Reply)
Discussion started by: Mathew_paul
1 Replies

2. UNIX for Beginners Questions & Answers

Using df -g command with awk to get SQL statement

Hi Gurus... good day; currently I trying to run the df -g command with awk to get to convert in SQL statement, but I have some errors; df -g | awk '{print "This is the FileSystem: " $NF, " This is LV: "$1, "This is SIZE: "$2, "This is FREE: " $3, "This is the USED% "$4}' This on AIX... (3 Replies)
Discussion started by: wcastibl
3 Replies

3. Shell Programming and Scripting

Convert Update statement into Insert statement in UNIX using awk, sed....

Hi folks, I have a scenario to convert the update statements into insert statements using shell script (awk, sed...) or in database using regex. I have a bunch of update statements with all columns in a file which I need to convert into insert statements. UPDATE TABLE_A SET COL1=1 WHERE... (0 Replies)
Discussion started by: dev123
0 Replies

4. Shell Programming and Scripting

Insert field values in a record using awk command

Hi Friends, Below is my input file with "|" (pipe) as filed delimiter: My Input File: HDR|F1|F2||||F6|F7 I want to inser values in the record for field 4 and field 5. Expected output HDR|F1|F2||F4|F5|F6|F7 I am able to append the string to the end of the record, but not in between the... (3 Replies)
Discussion started by: Ajay Venkatesan
3 Replies

5. Shell Programming and Scripting

How to add printf statement in awk command?

hi all i need to add the prinf statement in awk command for the converted comma separated output.... below is my code : Code Credits :RudiC awk -F, 'NF==2 {next} {ITM=$1 AMT=$2+0 CNT=$3+0 TOTA+=$2 ... (4 Replies)
Discussion started by: hemanthsaikumar
4 Replies

6. Shell Programming and Scripting

Command in inside awk statement

Hello can you please help me with below script which is meant to delete clients from multiple netbackup policies I want to run a command insdie awk statement apparelnlty this script is not working for me for i in $( cat clients_list) do bppllist -byclient $i | awk... (6 Replies)
Discussion started by: Sara_84
6 Replies

7. Shell Programming and Scripting

awk command to insert a tag in XML

Hi All, I need a help on inserting a XML tag. Actual input <var> <nam>abcd</nam> <a1>.</a1> </var> if tag <a1>.</a1> is getting missed in XML like below <var> <nam>abcd</nam> </var> i need to insert wherever it is missed after <nam> tag and before </var> tag. Could anyone... (3 Replies)
Discussion started by: mohanalakshmi
3 Replies

8. Shell Programming and Scripting

How to insert numbers to a in between statement

Hi Guys, I want to create a shell script that will give me the output below. I want to insert the numbers from the input file to my url addresses below. And from the numbers below, I want to separate the last digit with a period (i.e. from 222222222222 to 22222222222.2). Appreciate any help.... (14 Replies)
Discussion started by: pinpe
14 Replies

9. Shell Programming and Scripting

How is use sselect statement o/p in insert statement.

Hi All, I am using Unix ksh script. I need to insert values to a table using the o/p from a slelect statement. Can anybody Help! My script looks like tihs. ---`sqlplus -s username/password@SID << EOF set heading off set feedback off set pages 0 insert into ${TB_NAME}_D... (2 Replies)
Discussion started by: nkosaraju
2 Replies

10. Shell Programming and Scripting

How to convert unix command into Awk statement

Hi all, How can i use the below unix command in AWK . Can any one please suggest me how i can use. sed -e "s/which first.sh/which \$0/g" $shell > $shell.sal where $0=current program name(say current.sh) $shell=second.sh (1 Reply)
Discussion started by: krishna_gnv
1 Replies
Login or Register to Ask a Question