CODEWITHSUNDEEP
awksed
user2570205
user2570205

Reputation: 137

Find replace string the doesn't matching the specific pattern

I have hundreds of files containing like the below. In my sqlldr ' is the text qualifier and my files are rejected due to text like Wegman's , which contains apostrophe in the text itself.

Using sed/awk is there a way to find such string and replace 's with ` tick or something?

t2.txt';'20160707071500';'478251533';'TWN';'20160620160801';'1';'691891-2';'2';'0';'Employer';'1';'OMCProcessed';'Wegman's Food Market';'';'Wegman's Food Markets';'14411364807'

One solution I thought is, find text that is not equal to '; but not sure how to put to use.

Upvotes: 0

Views: 48

Answers (2)

karakfa
karakfa

Reputation: 67537

perhaps here sed is a better alternative

$ sed -r 's/([^;])(\x27)([^;])/\1\2\2\3/g' file

't2.txt';'20160707071500';'478251533';'TWN';'20160620160801';'1';'6918912';'2';'0';'Employer';'1';'OMCProcessed';'Wegman''s Food Market';'';'Wegman''s Food Markets';'14411364807'

Upvotes: 2

jas
jas

Reputation: 10865

The usual way to escape single quotes in SQL is to double them, but you can modify the call to gsub to replace them with whatever you like.

There's probably a fancier way to do this, but here I've simply stripped off the enclosing quotes from each field, replaced the interior quotes, and then assigned back to the original field with the enclosing quotes included again.

$ cat m.txt
't2.txt';'20160707071500';'478251533';'TWN';'20160620160801';'1';'691891-2';'2';'0';'Employer';'1';'OMCProcessed';'Wegman's Food Market';'';'Wegman's Food Markets';'14411364807'

$ cat m.awk
BEGIN { FS=OFS=";" }
{
    for (i=1; i<=NF; ++i) {
        f = substr($i,2,(length($i) - 2))
        gsub("'", "''", f)
        $i = "'" f "'";
    }
}1

$ awk -f m.awk m.txt
't2.txt';'20160707071500';'478251533';'TWN';'20160620160801';'1';'691891-2';'2';'0';'Employer';'1';'OMCProcessed';'Wegman''s Food Market';'';'Wegman''s Food Markets';'14411364807'

Upvotes: 1

Related Questions