Syntax error: "(" unexpected when using GNU sed with 'e' flag

Alex Gyoshev Published at Dev

Alex Gyoshev

Desired end result

I'm trying to convert the following (MS-SQL) string

INSERT INTO Foo (Bar) VALUES (CAST('1958-08-22 21:00:00.000' AS DateTime))

to SQLite syntax

INSERT INTO Foo (Bar) VALUES (-358491600)

Approach

I'm successfully doing this with the following sed arguments:

sed -r "s#cast\('(.*)' as datetime\)#date -d '\1' '+%s'#ige"

(calling date -d '...' '+%s' to convert the date to epoch)

Problem

Running the same command over the complete line:

echo "INSERT INTO Foo (Bar) values (cast('1958-08-22 21:00:00.000' as datetime))" | \
    sed -r "s#cast\('(.*)' as datetime\)#date -d '\1' '+%s'#ige"

...produces an error: sh: 1: Syntax error: "(" unexpected

From what I've tracked, parenthesis cause the line to fail:

echo "() cast('1958-08-22 21:00:00.000' as datetime)" | \
    sed -r "s#cast\('(.*)' as datetime\)#date -d '\1' '+%s'#ige"

Removing the e switch properly converts the command. What am I doing wrong?

Kent

this sed with ge flag does your job:

sed -r 's/(.*CAST[^\x27]*\x27)([^\x27]*)(\x27 AS DateTime.*)/
      echo "\1"$(date -d"\2" "+%s")"\3"/ge' file

with your example:

kent$  cat f
INSERT INTO Foo (Bar) VALUES (CAST('1958-08-22 21:00:00.000' AS DateTime));
INSERT INTO Foo (Bar) VALUES (CAST('1958-08-23 22:00:00.000' AS DateTime));
kent$  sed -r 's/(.*CAST[^\x27]*\x27)([^\x27]*)(\x27 AS DateTime.*)/echo "\1"$(date -d"\2" "+%s")"\3"/ge' file
INSERT INTO Foo (Bar) VALUES (CAST('-358488000' AS DateTime));
INSERT INTO Foo (Bar) VALUES (CAST('-358398000' AS DateTime));

if you don't want to have the As DateTime in output, just make proper groups, I think you can manage it.

Collected from the Internet

Please contact [email protected] to delete if infringement.