CODEWITHSUNDEEP
awksed
TK45
TK45

Reputation: 49

Replace all unwanted semicolon outside the quote or double quotes

I'm not quite good at bash. I have generated PSQL file from Excel with python to run psql in bash script.

I would like to replace all semicolon outside the quote or double quotes

SELECT *; FROM table ;WHERE description =';';

SELECT *; FROM table ;WHERE; description like "%;%";

SELECT *; FROM table ;;WHERE description like ";?";

THE Result should except semicolon in all type of quote/double quotes like this

SELECT * FROM table WHERE description =';'

SELECT * FROM table WHERE description like "%;%"

SELECT * FROM table WHERE description like ";?"

Here's what I tried

sed "s/.*;.*\(\'\;'\)//g" a.sql

seem it does nothing

SELECT *; FROM table ;WHERE description =';';

SELECT *; FROM table ;WHERE; description like "%;%";

SELECT *; FROM table ;;WHERE description like ";?";

Upvotes: 1

Views: 91

Answers (1)

anubhava
anubhava

Reputation: 785721

Here is a gnu awk solution using FPAT:

awk -v OFS= -v FPAT="\"[^\"]*\"|'[^']*'|[^'\"]+" '{
   for (i=1; i<=NF; ++i) if (!($i ~ /["\047]/)) gsub(/;/, "", $i)
} 1' file

SELECT * FROM table WHERE description =';'
SELECT * FROM table WHERE description like "%;%"
SELECT * FROM table WHERE description like ";?"

FPAT regex details:

  • \"[^\"]*\": Match a double quoted string

  • |: OR

  • '[^']*': Match a single quoted string

  • |: OR

  • [^'\"]+: Match 1+ of any characters that are not ' and "

  • if (!($i ~ /["\047]/)) gsub(/;/, "", $i): Removes ; if field doesn't start with " or '

Alternatively use this perl command line:

perl -pe 's/((["\047])(?:\\.|[^"\047])*\2)|;/$1/g' file

SELECT * FROM table WHERE description =';'
SELECT * FROM table WHERE description like "%;%"
SELECT * FROM table WHERE description like ";?"

Upvotes: 2

Related Questions