Using sed to replace commas with dot without touching dates

Based on your shown samples only, in case you are ok with awk, you could try following.

awk '
BEGIN{
  FS=OFS=";"
}
{
  for(i=1;i<=NF;i++){
    if($i~/^[0-9]+\.[0-9]+$/){
      sub(/\./,",",$i)
    }
  }
}
1
'  Input_file

OR in case you are looking single digit followed by . followed by 3 digits then you can try following.

awk '
BEGIN{
  FS=OFS=";"
}
{
  for(i=1;i<=NF;i++){
    if($i~/^[0-9]\.[0-9]{3}$/){
      sub(/\./,",",$i)
    }
  }
}
1
' Input_file

Yet another sed solution (tested with GNU sed, syntax might differ for other implementations)

This forms a loop by using a label a for the substitute command. As long as there's a match found, ta will jump to the label a. The match condition is presence of . character between exactly two sequences of digits. I have used + quantifier, which can be changed to * if needed.

sed -E ':a s/(;[0-9]+)\.([0-9]+(;|$))/\1,\2/; ta'

If the first column can also match, use this:

sed -E ':a s/((^|;)[0-9]+)\.([0-9]+(;|$))/\1,\3/; ta'

This is easier to do with lookarounds:

perl -pe 's/(^|;)\d+\K\.(?=\d+(;|$))/,/g'

See my tutorial Emulating regexp lookarounds in GNU sed for more such workarounds in GNU sed when lookarounds are needed.

I suggest to use awk as it is simpler to read / write:

awk -F\; 'BEGIN{OFS=FS}{for(i=3;i<=NF;i++){sub(/\./,",",$i)}}1'

in multiline form:

awk -F\; '
BEGIN{OFS=FS}
{
    # starting with the 3rd field, replace '.' by ','
    for(i=3;i<=NF;i++){
        sub(/\./,",",$i)
    }
}
1 # print the line'

With GNU sed which supports \b (word boundary), how about:

sed -E 's/\./,/g; s/\b([0-9]{4}),([0-9]{2}),([0-9]{2})\b/\1.\2.\3/g' file

It once replaces all dots with commas, then restores back again within dates. I suppose this is not robust enough and there will be some edge cases which do not work well.

This problem can be solved with sed, but it is not as straightforward as you imagine. Even when using GNU sed and exploit the use of word-boundaries, could lead to wrong matches.

Let's imagine that bre is the Basic regular expression which matches the fields you are interested in. If your strings looks like:

foo;bar;bre;baz;qux
foo;bar;brebaz;qux
foo;bar;bre;bre;baz;qux

This would match bre in all cases, even if we are not interested in brebaz. So you could prepend it with ; and try to match ;bre. This would still match ;brebaz. But if we add it to the end as well (i.e. ;bre;) it will not match the second ;bre; in the third example due to the overlap.

The trick is now to do the following actions:

1. double the field separators (; → ;;)
2. sandwich the string between field separators (string → ;string;)
3. do the match on ;bre;
4. remove the sandwich and un-double the field separators (; ← ;;)

The following regex defines a floating point number:

[-+]?[0-9]+[.]?[0-9]*([eE][-+]?[0-9]+)?

This will match any of the forms 123, 123.45, 123.45E+6. Since we want to replace the <dot>-character into a <comma>-character, we just need to modify it a bit. Furthermore, we know that the first column is not a number, so it always starts with a <semicolumn>-character. The modified regex would look like:

(;[-+]?[0-9]+)[.]([0-9]*([eE][-+]?[0-9]+)?;)

So performing all the actions above; with converting the above to a proper sed expression (escaping all the needed characters), we get:

sed -e 's/;/;;/g;s/.*/;&;/;s/\(;[-+]\?[0-9]\+\)[.]\([0-9]*\([eE][-+]\?[0-9]\+\)\?;\)/\1,\2/g;s/;;/;/g;s/;\(.*\);/\1/'

This might work for you (GNU sed):

sed -E 'G;:a;s/([0-9]{4}\.[0-9]{2}\.[0-9]{2})\n/\n\1/;ta;s/\.\n/\n,/;ta;s/(.)\n/\n\1/;ta;s/.//' file

Append a newline to each line and then parse the line, removing the newline when the line has been parsed.

There are 3 rules to look for:

• a date i.e. yyyy.mm.dd\n, replace by \nyyyy.mm.dd where y,m and d are integers
• a period i.e. .\n, replace by \n, this is required action.
• an arbitrary character e.g. x\n, replace by \nx

Repeat until all rules fails and then remove the newline at the start of the line.