Code_rocks Code_rocks - 1 month ago 5
Bash Question

Delete all string after last matching words

I want to delete all string after last where condition

My input is

DELETE FROM abc T1 WHERE EXISTS (SELECT 1 FROM cdef T2 WHERE T1.a=T2.b)


Want the output as

DELETE FROM abc T1 WHERE EXISTS (SELECT 1 FROM cdef T2 WHERE


i have tried it with sed command as

output=`echo DELETE FROM abc T1 WHERE EXISTS (SELECT 1 FROM cdef T2
WHERE T1.a=T2.b) | sed -n -e 's/[Ww][Hh][Ee][Rr][Ee].*//p'`


but i got output as


DELETE FROM abc T1

Answer

With sed, using BRE:

sed 's/\(.*WHERE\).*/\1/;s/\(.*where\).*/\1/;' <<< "DELETE FROM abc T1 WHERE EXISTS (SELECT 1 FROM cdef T2 WHERE T1.a=T2.b)"

With GNU sed, using the i (for case insensitive) modifier:

sed 's/\(.*where\).*/\1/i' <<< "DELETE FROM abc T1 WHERE EXISTS (SELECT 1 FROM cdef T2 WHERE T1.a=T2.b)"

or the alternation | operator:

sed -r 's/(.*(where|WHERE)).*/\1/i' <<< "DELETE FROM abc T1 WHERE EXISTS (SELECT 1 FROM cdef T2 WHERE T1.a=T2.b)"

output:

DELETE FROM abc T1 WHERE EXISTS (SELECT 1 FROM cdef T2 WHERE