Replace single quote at beginning of columns

Question

I receive input files that are outside my control that contain a leading single quote in some columns, such as

'foo|'012|that's nice|bar

I want to remove all leading single quotes from each field, to get expected output:

foo|012|that's nice|bar

With awk, I assumed gsub regex metacharacters like ^ worked per-column, but it appears to only work at the beginning of a line:

$ echo "'foo|'012|that's nice|bar" | awk -F'|' '{gsub(/^'\''/,"")}1'
foo|'012|that's nice|bar

How can I remove leading single quote from each column?

Answer 1

No need for awk, sed can do it with:

sed -E "s/(^|\|)'/\1/g"

The -E option to switch to extended regular expressions will be in the next version of the POSIX standard but is already supported by most sed implementations. Or you could use perl which supersedes both sed and awk:

perl -pe 's/(^|\|)'\''/$1/g'

Or:

perl -pe "s/(^|\|)\K'//g"

(\K to mark the start of what is Kept from the match).

Or:

perl -pe "s/(?<![^|])'//g"

(replaces ' as long as it's not preceded by a character other than |).

Or with its awk mode:

perl -F'\|' -pe 's/^'\''// for @F; $_ = join "|", @F'

With awk -F'|', you'd need to apply the substitution to every field, like in perl's awk mode above:

awk -F'|' -v OFS='|' '
  {
    for (i = 1; i <= NF; i++) sub(/^'\''/, "", $ i)
    print
  }'

For awk, when the field separator is a single character, as a special case, it is not treated as a regular expression, so you don't need to escape that |.

$ in awk is a unary operator that expects a number and returns the corresponding field if it's a number in between 1 and NF, the whole record if that number is 0 or empty strings¹ otherwise.

sub() and gsub() can take 2 or 3 arguments, if the third argument (the (one and only) subject of the substitution) is not supplied, then it defaults to the whole record ($0). gsub() differs from sub() the same way as s/x/y/g differs from s/x/y/ in sed. sub() replaces only the first occurrence of the pattern, while gsub() replaces all of them.

Here the regex can match only once since it's anchored at the start, so sub() and gsub() won't make a difference.

IOW, gsub() is not about doing one substitution in each of the fields, it's about doing all the substitutions in one string, that string being the whole unsplit record by default.

---

^{¹ technically, they're treated as numeric strings. That is, they are treated as numbers if they look like a number and string otherwise. An empty string is treated as a string.}

Answer 2

You would have to iterate over the fields and replace the quote character in each. Stéphane shows how to do this using awk in their answer.

$ mlr --csv --fs pipe -N put 'for (k,v in $*) { $[k] = sub(v, "^\047", "") }' file
foo|012|that's nice|bar

This uses Miller (mlr) to read the input as a header-less CSV data set (with pipes as field delimiters). For each record, the put expression iterates over all fields and removes the first character from them if it is a single quote (047 in octal).

---

Another approach with Miller which uses apply() to apply a function that deletes an initial single quote from a field. The function is applied to each field in each record.

$ mlr --csv --fs pipe -N put '$* = apply($*, func(k,v) { return { k: sub(v, "^\047", "") } })' file
foo|012|that's nice|bar

Answer 3

Using GNU awk for gensub() (also supported in some other awks but not required by POSIX yet):

$ awk '{$0=gensub(/(^|\|)\047/,"\\1","g")} 1' file
foo|012|that's nice|bar

I could have just done print gensub(...) above which would be a bit more efficient but I'm assigning to $0 and printing with 1 for consistency with the other answers below and in case you actually need to do something with fields after the substitution.

Alternatively, using GNU awk for RT:

$ awk -v RS='|' '{ORS=RT; sub(/^\047/,"")} 1' file
foo|012|that's nice|bar

or using any awk:

$ awk '{gsub(/\|\047/,"|"); sub(/^\047/,"")} 1' file
foo|012|that's nice|bar

or also using any awk:

$ awk -F "[|]'" -v OFS='|' '{$1=$1; sub(/^\047/,"")} 1' file
foo|012|that's nice|bar

See http://awk.freeshell.org/PrintASingleQuote for why I'm using \047 to represent '.

All of the awk string-manipulation functions (*sub(), match(), *split(), index(), substr(), length(), etc.), operate on whatever string you give them as an argument with, for those that don't require a string argument, the default being $0 if no string is supplied as an argument. They don't separate the string into fields or anything else before operating on it so if you did want to change one field at a time for some reason then you'd need to write a loop to call sub() on each field one at a time:

$ awk 'BEGIN{FS=OFS="|"} {for (i=1; i<=NF; i++) sub(/^\047/,"",$i)} 1' file
foo|012|that's nice|bar

but that'd be less efficient than using either of these:

awk '{$0=gensub(/(^|\|)\047/,"\\1","g")} 1' file
awk '{gsub(/\|\047/,"|"); sub(/^\047/,"")} 1' file

since:

1. It's calling *sub() NF times per line instead of just 1 or 2 times.
2. It's referring to a field $i which forces awk to do field splitting while if you don't refer to a field in your script (as in the subsequent 2 scripts above) then most awks won't take the time to split each record into fields.
3. It's forcing awk to reconstruct $0 from the fields every time a call to sub() changes a field, i.e. up to NF times per line.

Answer 4

Using Raku (formerly known as Perl_6)

Using <(…)> capture-markers:

~$ raku -pe "s:g/  [ ^ | \| ]  <( \' )> //;"  file

#OR

~$ raku -pe 's:g/  [ ^ | \| ]  <( \c[APOSTROPHE] )> //;'  file

OR using <?after … > positive Lookbehind:

~$ raku -pe "s:g/  <?after ^ | \| >  \'  //;"  file

#OR

~$ raku -pe 's:g/  <?after ^ | \| >  \c[APOSTROPHE]  //;'  file

OR using <!after … > negative Lookbehind with <-[…]> negative custom character-class¹:

~$ raku -pe "s:g/  <!after  <-[|]> >  \'  //;"  file

#OR

~$ raku -pe 's:g/  <!after  <-[|]> >  \c[APOSTROPHE]  //;'  file

OR using split/join:

~$ raku -ne ".split(/ [ ^ | \| ] \'? /)[1..*].join('|').put"  file

#OR

~$ raku -ne '.split(/ [ ^ | \| ] \c[APOSTROPHE]? /)[1..*].join("|").put'  file

Note the use of square bracket-grouping above where necessary (square brackets in Raku do not capture into $0, $1, etc.).

---

Sample Input (testing empty column as well):

'foo|'012|that's nice|bar||baz

Sample Output (all examples):

foo|012|that's nice|bar||baz

---

^{¹ Thanks to @StéphaneChazelas for the Perl example.}

https://docs.raku.org/language/regexes
https://raku.org