CODEWITHSUNDEEP
bashsed
Alex Gyoshev
Alex Gyoshev

Reputation: 11977

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

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?

Upvotes: 1

Views: 452

Answers (2)

Kent
Kent

Reputation: 195249

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.

Upvotes: 2

Slava Semushin
Slava Semushin

Reputation: 15214

If you run your command under strace to see what exacly will be executed you will see the following:

$  echo "INSERT INTO Foo (Bar) values (cast('1958-08-22 21:00:00.000' as datetime))" | strace -ff sed -r "s#cast\('(.*)' as datetime\)#date -d '\1' '+%s'#ige" 2>&1 | grep 'execve('
execve("/bin/sed", ["sed", "-r", "s#cast\\('(.*)' as datetime\\)#dat"...], [/* 27 vars */]) = 0
[pid  8179] execve("/bin/sh", ["sh", "-c", "INSERT INTO Foo (Bar) values (da"...], [/* 27 vars */] <unfinished ...>

It means that sed tries to execute not only text that was matched the pattern but the whole line.. So, probably, you can't do that with sed (I will be glad if I'm mistaken.)

So, I suggest to gather all dates from file, convert them and then replace one by one. For example:

$ cat q.sql
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));
$ sed "s|.*CAST('\([^']\+\)'.*|\1|" q.sql | while read DATE; do sed -i "s|$DATE|$(date -d "$DATE" '+%s')|" q.sql; done
$ cat q.sql
INSERT INTO Foo (Bar) VALUES (CAST('-358495200' AS DateTime));
INSERT INTO Foo (Bar) VALUES (CAST('-358405200' AS DateTime));

Upvotes: 2

Related Questions