I have a file which represent a list of SQLs where caluses. I want to remove everything that is not the columns names, and, or and where(case insensitive). In addition if it can also remove new lines between each where it would be perfect.

For example:

[/home]$ cat file.txt
wheRe (a='asd') ANd t.b='esd'
WHERE B = 'xcz' or c in ('asd , 'asd')
WHEre C='zxc'
 and t.a    = 'asd'

#running the commadn will generate:
[/home]$ remove_evreything_except "a b c and or where"
where a and b
where b or c
where c and a


Dirty version; grep -o -i -E "where|and|or|\(|\)|[ |\.|(|\"][a|b|c][\"| |=]" file.txt | tr '[:lower:]' '[:upper:]'| sed 's/ //g' | tr '\n' ' ' |sed 's/[\.||=|\"]//g' | sed 's/WHERE/\nWHERE/g' | sed 's/( /(/g' | sed 's/ )/)/g' | sed 's/()//g'

  • This would probably require an SQL parser. – Kusalananda Feb 25 at 12:33
  • I think bash would be simpler :) working on one now – Nir Feb 25 at 12:40
  • Do you always have just where columnX and/or columnY ? Otherwise it will be indeed difficult... – RoVo Feb 25 at 13:04
  • 1
    You need a proper sql parser, otherwise how do you distinguish an a as a variable from a as a part of the string 'asd'? – jimmij Feb 25 at 13:21
  • You are right, my dirty version assume the column would be on the left. That's not good. – Nir Feb 25 at 13:25

Your Answer

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

Browse other questions tagged or ask your own question.