1

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?

Alex Gyoshev
  • 11,929
  • 4
  • 44
  • 74

2 Answers2

2

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));
Slava Semushin
  • 14,904
  • 7
  • 53
  • 69
  • Thanks for sharing the `strace` technique, very handy for debugging. The solution is nice, too (if I'm not wrong, combining `uniq` can make it even faster if there are many duplicates). – Alex Gyoshev Oct 24 '14 at 12:55
2

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.

Kent
  • 189,393
  • 32
  • 233
  • 301
  • Interesting implementation! But as far I understand it doesn't handle case when more than one date located on the same line. – Slava Semushin Oct 24 '14 at 12:30
  • @SlavaSemushin I think it could be done. well if we are talking about the "doability" only. we can nest `sed/awk/grep/....` in `sed`. just a matter of complexity. but it could be done by gnu sed. – Kent Oct 24 '14 at 12:32
  • The `echo $(date)` is a bit of a wart, though. Provided there are no percent signs in the matched text, `date -d"\2" +"\1%s\3"` might be easier on the eyes and nerves. – tripleee Oct 24 '14 at 12:51
  • Thanks, @Kent! You mean that doing global matches would require the expression to be like shown in [your answer here](http://stackoverflow.com/a/11719531/25427), right? – Alex Gyoshev Oct 24 '14 at 12:54
  • @AlexGyoshev yes. You asked sed s/../../ge, so I answered it. but if the case is more complex, other way (perl/awk/script..) may better than sed. – Kent Oct 24 '14 at 12:57
  • Yup, marking it as answer since it answers the question. I ended up doing something similar to @SlavaSemushin's solution in the end (although via a node script). More verbose, but cleaner imo. – Alex Gyoshev Oct 24 '14 at 13:13