Making case insensitive in awk

here is a statement

awk '/CREATE PROCEDURE/,/[dD]elimiter/' "$file1" > onlyproc1.sql

which mean cut from create procedure to Delimiter or delimiter and paste it in onlyproc1.sql... my query is how to make this case insensitive.. that is i want the above code to work whther it is Delimiter or delimiter or DEMILITER..

do i have to use something like this or is there any shortway to make it case insensitive...?

awk '/CREATE PROCEDURE/,/[dD][eE][lL][iI][mM][iI][tT][eE][rR]/' "$file1" > onlyproc1.sql

Not sure if mine is shorter

awk '/CREATE PROCEDURE/,/delimiter|DELIMITER/' "$file1" > onlyproc1.sql

One more thing, yours has an advantage of picking data even if it in this format "DeLiMiTeR" i.e. a mixed case!
Mine will look only for "delimiter" or "DELIMITER"

--ahamed

thanks but would it work for Delimiter... that is first letter capital...
so if i do it this way

awk '/CREATE PROCEDURE/,/delimiter|DELIMITER|Delimiter/' "$file1" > onlyproc1.sql

it should work right?

Yes, that should also work.

If you think only the fist letter is going to be an issue, you can do this... /[Dd]elimiter/

If you are uncertain which is gonna be what, I think your solution holds good!

--ahamed

thanks i tried

awk '/CREATE PROCEDURE/,/delimiter|DELIMITER|Delimiter/' "$file1" > onlyproc1.sql

it worked fine :-)..

awk '/CREATE PROCEDURE/,/[Dd]elimiter|DELIMITER/' "$file1" > onlyproc1.sql

--ahamed

oh this one is better.. thanks

---------- Post updated at 02:56 PM ---------- Previous update was at 02:51 PM ----------

sed -n "/$firstword(/,/[dD]elimiter|DELIMITER/p" onlyproc1.sql | awk '!x[$1]++' > procextract1.sql

is there something wrong in above...? cause earlier i used to do

sed -n "/$firstword(/,/[dD]elimiter/p" onlyproc1.sql | awk '!x[$1]++' > procextract1.sql

it was working fine but when i added '|' to it which i thought would be similiar syntax to awk, the above statement is not functioning properly :wall:

Try escaping the pipe...

sed -n "/$firstword(/,/[dD]elimiter\|DELIMITER/p" onlyproc1.sql | ...

--ahamed

thanks a lot.. it worked

---------- Post updated 28-12-11 at 12:31 PM ---------- Previous update was 27-12-11 at 04:05 PM ----------

One more hiccup again....
here is the content of a file..

cat views.sql
DROP VIEW IF EXISTS View11;
        CREATE VIEW View11
        AS
           SELECT User.id AS userId,
                  User.userCDIId AS userCDIId,
                          Button.id AS buttonId,
                  includeInCallHistory,
                  data AS pOCData,
                  Button.pointOfContactId AS pointOfContactId
            FROM User, UserCDI, Button, PointOfContact
            WHERE User.userCDIId = UserCDI.id
                  AND Button.parentUserCDIId = UserCDI.id
                  AND Button.pointOfContactId = PointOfContact.id
                  AND buttonType = 'PointOfContact';
 
        DROP VIEW IF EXISTS View22;
        CREATE VIEW View22
        AS
           SELECT User.id AS userId,
                  User.userCDIId AS userCDIId,
                  data AS personalPOCData,
                  Button.personalPointOfContactId AS personalPointOfContactId
            FROM User, UserCDI, Button,PersonalPointOfContact
            WHERE User.userCDIId = UserCDI.id
                  AND Button.parentUserCDIId = UserCDI.id
                  AND Button.personalPointOfContactId = PersonalPointOfContact.id
                  AND buttonType = 'PersonalPointOfContact';

i am trying to extract one view at a moment starting from create view to ';' which marks its end...
i was using the below few commands

firstword="CREATE VIEW View11"
 
 sed -n "/$firstword\$/,/;/p" views.sql | awk '!x[$1]++' > procextract.sql 
#the above commands extracts that prticular view and paste to procextract.sql
 
echo "`sed "/$firstword\$/,/;/d" views.sql `" > views.sql 
#the above command deletes that particular view from the views.sql file
 

when i tried the command say first command which extracts a particular view its not exactly cutting it from "create view view_name" to ";" rather last one or two lines are missing..
here is the output..

cat procextract.sql
        CREATE VIEW View11
        AS
           SELECT User.id AS userId,
                  User.userCDIId AS userCDIId,
                          Button.id AS buttonId,
                  includeInCallHistory,
                  data AS pOCData,
                  Button.pointOfContactId AS pointOfContactId
            FROM User, UserCDI, Button, PointOfContact
            WHERE User.userCDIId = UserCDI.id
                  AND Button.parentUserCDIId = UserCDI.id

here last two lines are missing that is

AND Button.pointOfContactId = PointOfContact.id
AND buttonType = 'PointOfContact';

are missing.. so how to rectify this bug....?

---------- Post updated at 01:44 PM ---------- Previous update was at 12:31 PM ----------

can anyone help me on this one...?

$ awk '/CREATE VIEW View11/,/;$/ {print}' views.sql
CREATE VIEW View11
AS
   SELECT User.id AS userId,
          User.userCDIId AS userCDIId,
          Button.id AS buttonId,
          includeInCallHistory,
          data AS pOCData,
          Button.pointOfContactId AS pointOfContactId
    FROM User, UserCDI, Button, PointOfContact
    WHERE User.userCDIId = UserCDI.id
          AND Button.parentUserCDIId = UserCDI.id
          AND Button.pointOfContactId = PointOfContact.id
          AND buttonType = 'PointOfContact';

the bug is still persisting...

 firstword="CREATE VIEW View11"
[root@dunkin-ds-dev-103 vivek]# awk "/$firstword/,/;$/ {print}" tble2 | awk '!x[$1]++' > procextract2.sql
[root@dunkin-ds-dev-103 vivek]# cat procextract2.sql
        CREATE VIEW View11
        AS
           SELECT User.id AS userId,
                  User.userCDIId AS userCDIId,
                          Button.id AS buttonId,
                  includeInCallHistory,
                  data AS pOCData,
                  Button.pointOfContactId AS pointOfContactId
            FROM User, UserCDI, Button, PointOfContact
            WHERE User.userCDIId = UserCDI.id
                  AND Button.parentUserCDIId = UserCDI.id
[root@dunkin-ds-dev-103 vivek]#

is it because i used variable $firstword...?
--------------------------------------------------------
i found out.. its because of this | awk '!x[$1]++'
when i executed the command excluding this, i was getting proper output... but that is required in case there are more than one view of same name..

with awk

$ awk 'BEGIN{ IGNORECASE=1 } /CREATE PROCEDURE/,/delimiter/' "$file1" > onlyproc1.sql

Why are you using ... | awk '!x[$1]++' ? Try without that, it is working... I understand awk is for uniquel ines, but thats not required here.

--ahamed

---------- Post updated at 01:37 AM ---------- Previous update was at 01:35 AM ----------

Ok, just read your post that you found out the issue. For eliminating duplicate ones, you will need to put in some login.

--ahamed

i removed that awk '!x[$1]++' from the code and its working fine but what if there are two views of the same name..? two views will be present in the extracted file (if that scenario comes)...

---------- Post updated at 04:37 PM ---------- Previous update was at 03:39 PM ----------

is there any way i could copy only one of the duplicates into a file rather than 2?

Try this...

awk '{if($3~v){a=1}}a;/;/{exit}' v=View11  infile

--ahamed

thanks a lot.. the above code worked..

---------- Post updated at 06:18 PM ---------- Previous update was at 06:00 PM ----------

hey i got a bug for the above code.. here is the output..

 name="\`mfuBucketUpdate\`"
[root@dunkin-ds-dev-103 vivek]# echo "`awk '{if($3~v){a=1}}a;/;/{exit}' v="$name" procextract2.sql`" > procextract2.sql
[root@dunkin-ds-dev-103 vivek]# vi procextract2.sql
[root@dunkin-ds-dev-103 vivek]# cat procextract2.sql
CREATE PROCEDURE `mfuBucketUpdate`(IN chId int, OUT mfId int, OUT uId int)
   BEGIN
      DECLARE vcount    int DEFAULT 0;
[root@dunkin-ds-dev-103 vivek]#

the initial contents of procextract2.sql is

cat procextract2.sql
CREATE PROCEDURE `mfuBucketUpdate`(IN chId int, OUT mfId int, OUT uId int)
   BEGIN
      DECLARE vcount    int DEFAULT 0;
      DECLARE vId       int DEFAULT 1;
      DECLARE vpocId    int;
      DECLARE vppocId   int;
      DECLARE vuserId   int;
      SET vpocId =
             (SELECT pointOfContactId
                FROM CommunicationHistory CH
               WHERE CH.id = chId);
      SET vppocId =
             (SELECT personalPointOfCon
etc etc 
.
.
.

the procextract2.sql has only one procedure.... for this condition it worked when i tried with some other procedure but for this procedure its not working....

It is very obvious that it will not work!
It is not a bug, we were dealing with views till now and suddenly you being in procedures and say it is not working?

--ahamed

i was using the code for both procedures and views.... but the code should work for any names right either procedures or views

echo "`awk '{if($3~v){a=1}}a;/;/{exit}' v="$views" procextract2.sql`" > procextract2.sql

in the code /;/ mentioned specifies the end point right?

so

echo "`awk '{if($3~v){a=1}}a;/[dD]elimiter\|DELIMITER/{exit}' v="$views" procextract2.sql`" > procextract2.sql

is for procedures right.... but what is that ';' after 'a' is it the syntax or view specific?

";" after "a" is syntax

--ahamed

cat tablextract2.sql
CREATE PROCEDURE after72DeleteTgr(id int)
BEGIN
END
$$
Delimiter ;
CREATE PROCEDURE after72DeleteTgr(id int)
BEGIN
END
$$
Delimiter ;
[root@dunkin-ds-dev-103 vivek]#
[root@dunkin-ds-dev-103 vivek]# proc_name1="after72DeleteTgr"
[root@dunkin-ds-dev-103 vivek]#
[root@dunkin-ds-dev-103 vivek]#  echo "`awk '{if($3~v){a=1}}a;/[dD]elimiter\|DELIMITER/{exit}' v="$proc_name1" tablextract2.sql`" > tablextract2.sql
[root@dunkin-ds-dev-103 vivek]# cat tablextract2.sql
CREATE PROCEDURE after72DeleteTgr(id int)
BEGIN
END
$$
Delimiter ;
CREATE PROCEDURE after72DeleteTgr(id int)
BEGIN
END
$$
Delimiter ;

is this because $3 will be looking for "after72DeleteTgr" and not after72DeleteTgr(...) as the startpoint....?

---------- Post updated at 02:17 PM ---------- Previous update was at 12:06 PM ----------

........