2

Consider some data that looks like this:

"2019-12-12 00:00:01","2012-01-01 01:01:01"

I wish to replace it so that they are valid datetime json values:

"2019-12-12T00:00:01+01","2012-01-01T01:01:01+01"

I tried writing the following sed command:

sed 's/"([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]) ([0-9][0-9]:[0-9][0-9]:[0-9][0-9])"/\1T/g' test.csv > testnew.csv

However, this gives the follow error:

sed: -e expression #1, char 99: invalid reference \1 on `s' command's RHS

Why is this happening, and how do I refer to the submatches of a regex search?

Jeff Schaller
  • 67,283
  • 35
  • 116
  • 255
paul23
  • 123
  • 1
    ( and ) are literal in BRE - so nothing is being captured. Use \( and \) or switch to extended mode (-E or -r). – steeldriver Aug 20 '19 at 00:13
  • 2
    See also Why does my regular expression work in X but not in Y? for a nice discussion of BRE versus ERE versus PCRE regex flavors – steeldriver Aug 20 '19 at 00:22
  • If your input is json please consider adding a full working in- and output, so that we can propose a good solution. I have the feeling that sed is not really the way to go here ... e.g. a Python solution might be way easier and better. – pLumo Aug 20 '19 at 07:23
  • @pLumo above is the full input & ouptut - it's a csv which contains rows of "date time values", in the format show. and I have to convert them to valid ISO 8601 strings. - For import using COPY in postgresql. – paul23 Aug 20 '19 at 11:06

1 Answers1

0

As @steeldriver mentioned you'll need to either escape your () like so:

sed 's/"\([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]\) \([0-9][0-9]:[0-9][0-9]:[0-9][0-9]\)"/"\1T\2+01"/g' test.csv > testnew.csv

Or use extended regex with -E or -r depending on your version of sed. Which also gives us the advantage of being able to clean up the groups a bit without making a picket fence:

sed -E 's/"([0-9]{4}-[0-9]{2}-[0-9]{2}) ([0-9]{2}:[0-9]{2}:[0-9]{2})"/"\1T\2+01"/g' test.csv > testnew.csv

I've added '\2+01' into the replace on both of those as that appears to be what is needed to make your desired output. If I misunderstood you'll probably want to change that

Vivian
  • 431
  • 3
  • 6