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?

  • 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 0: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 0: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 7: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
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.