[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

How to search and replace specific patterns ignoring case using sed or awk ?

Hi,

I have a file with a lot sqls :

Update test set t1='y' where t2='k';
update test set t1='H' where t2='l';
inserT into test values ('j','h');
deLete from test where t1='n';
deLEte from test where t1='m';
...............................................

I need to change the keywords : update, insert, delete, set, where etc to upper case irrespective of their current cases ... ie., the output should be like :

UPDATE test SET t1='y' WHERE t2='k';
UPDATE test SET t1='H' WHERE t2='l';
INSERT into test values ('h','j');
DELETE from test WHERE t1='n';
DELETE from test WHERE t1='m';
...............................................

How would I go about doing this using sed or awk ? ... any one-liners would be highly appreciated ...

Thanx

-RJ


0
vrrajeeb
Asked:
vrrajeeb
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
tfewsterCommented:
There's gotta be a better way, but this would work:

awk '{ for (i=1; i<=NF; i++)
  {
  if ( toupper($i) == "WHERE" ) { $i = "WHERE" }
  if ( toupper($i) == "SET" )   { $i = "SET" }
  }
print $0
}' a
0
 
tfewsterCommented:
Or, maybe a bit better:

awk '{ for (i=1; i<=NF; i++)
  {
  UC = toupper($i)
  if ( UC == "WHERE" || UC == "SET" || UC == "UPDATE" )
     { $i = UC }
  }
print $0
}' yourfile
0
 
vrrajeebAuthor Commented:
Thanx for the prompt response ...

But it didn't work ...

I saved the above code to a file and executed it and it didn't change anything ... and, in the if loop we are looking for WHERE, SET, UPDATE (all upper cases) right ?  ... so will this find where, set, update etc (lower cases) ?

Thanx

-RJ
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
koppchaCommented:
perl -i -pe 's/(update|insert|delete|set)/uc($1)/gie' text.txt
0
 
koppchaCommented:
I forgot where you can add what ever you want to the list
perl -i -pe 's/(update|insert|delete|set|where)/uc($1)/gie' text.txt

text.txt is your SQL file
0
 
vrrajeebAuthor Commented:
The perl solution changed all update to uc(update), insert to uc(insert) and so on ...

I made the awk solution work by using nawk instead of awk ...

nawk '{for(i=1;i<=NF;i++){UC=toupper($i);if UC=="WHERE"||UC=="SET"||UC=="UPDATE"||UC=="INSERT"||UC=="DELETE"){$i=UC}} print $0}' p.sql > p1.sql

0
 
koppchaCommented:
You might have missed 'e' at the end of the perl statement (/gie)

perl -i -pe 's/(update|insert|delete|set|where)/uc($1)/gie' text.txt
0
 
vrrajeebAuthor Commented:
Yeah, it works ... maybe I typed it wrong earlier ... but it updates the source file itself ... how to pipe it to a different file ?
0
 
tel2Commented:
Never pipe ("|") to a file.  Pipe to a process.  Redirect (">") to a file.

Remove the "-i", and add "> testnew.txt to the end, like this:
    perl -pe 's/(update|insert|delete|set|where)/uc($1)/gie' text.txt >testnew.txt
0
 
koppchaCommented:
Thanks tel2
wasn't able to see the comment till now
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now