Consider the following line of text (taken from a MySQL insert):

"description" varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

I want to return

"description" varchar(255) NOT NULL DEFAULT '',

I.e. I want to remove COLLATE utf8_unicode_ci. However, the collation following the COLLATE keyword can vary depending on the original Database schema.

I've come across lookaheads, but not quite sure how I could use it to remove the word.

  • 1
    awk '/COLLATE/ {$3=$4=""}1' file...
    – jasonwryan
    Commented Sep 7, 2015 at 8:55
  • That outputs the word following the match, how would I then remove it?
    – Amo
    Commented Sep 7, 2015 at 9:08
  • It removes both COLLATE and the field immediately after it; isn't that what you were after?
    – jasonwryan
    Commented Sep 7, 2015 at 9:10
  • Apologies, yes that worked. Feel free to write your solution as an answer so I can mark it as such.
    – Amo
    Commented Sep 7, 2015 at 9:14

1 Answer 1


Assuming that the field after COLLATE contains no whitespace, you can use a simple Awk one-liner:

 awk '/COLLATE/ {$3=$4=""}1' file

This matches on the pattern COLLATE and then prints the entire file without the third and fourth field on the matching record (line).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .