0

Background: I am moving some sql statements from SQL server to Oracle. While Oracle is case sensitive in string comparison, SQL server is not, by default I think. In my case all strings have to be in upper case.

in the original I might have something like 

xyz like '%foo%' and ...

which I want to convert to 

xyz like '%FOO%' and ...

i.e. anything between quotes following 'like ' has to be capitalised.

here is what I have tried and does not work:

echo "like 'aaa' aaa"|sed 's/like\( '.*'\)/like\U\1/g'

for some reason this gives me 

like 'AAA' AAA

I don't understand why it matches the whole line, even after the second quote.

This does also not work:

echo "like 'aaa' aaa"|sed 's/like\( '[^']*\)/like\U\1/g'

why? I don't understand the error message sed: -e expression #1, char 26: unterminated 's' command

Any ideas? Thanks a lot!

codeforester
  • 39,467
  • 16
  • 112
  • 140
Willi Fischer
  • 455
  • 6
  • 21

2 Answers2

3

When using 's/SEARCH/REPLACE/' with outer single quotes, you cannot use single quotes inside it as well... but there is a workaround with using \x27 instead

$ echo "like 'aaa' aaa"|sed 's/like\( \x27.*\x27\)/like\U\1/g'
like 'AAA' aaa


These two are equivalent, 's/like\( '.*'\)/like\U\1/g' is simply three strings concatenated 's/like( ' and .* and '\)/like\U\1/g'

$ echo "like 'aaa' aaa" | sed 's/like\( '.*'\)/like\U\1/g'
like 'AAA' AAA
$ echo "like 'aaa' aaa" | sed 's/like\( .*\)/like\U\1/g'
like 'AAA' AAA

$ # a different example
$ echo "like 'aaa' aaa" | sed 's/like/'LIKE'/g'
LIKE 'aaa' aaa


Also, .* will try to match as much as possible, so use this instead:

$ echo "xyz like '%foo%' and 'bar' ..." | sed 's/\(like[^\x27]*\x27\)\([^\x27]*\)/\1\U\2/g'
xyz like '%FOO%' and 'bar' ...

vs

$ echo "xyz like '%foo%' and 'bar' ..." | sed 's/like\( \x27.*\x27\)/like\U\1/g'
xyz like '%FOO%' AND 'BAR' ...
  • like[^\x27]*\x27 matches like followed by non-single quote character and a single quote character
  • [^\x27]* then we match as many non-single quote characters as possible which will then be subjected to \U in replacement section
Sundeep
  • 23,246
  • 2
  • 28
  • 103
  • Thanks! I suspected sth like that. I tried escaping the quote with a backslash, but that didn't work. So I assumes sed would determine the end of the command after the third slash. One question though: You know why my first example even worked then? – Willi Fischer Mar 22 '17 at 16:35
  • 'worked' in a way of not crashing and producing part of the desired output? – Willi Fischer Mar 22 '17 at 16:36
  • 1
    that is because `'s/like\( '.*'\)/like\U\1/g'` is actually multiple strings concatenated... `s/like\( ` and `.*` and `\)/like\U\1/g`... the `.*` happily matched everything... it is equivalent to `s/like\( .*\)/like\U\1/g` ... also see `echo "like 'aaa' aaa" | sed 's/like/'LIKE'/g'` for example – Sundeep Mar 22 '17 at 16:41
  • 1
    Thanks. this helped my understanding. Your edited answer is then I think almost equivalent to my second try, when using \x27 instead of the single quote? – Willi Fischer Mar 22 '17 at 16:50
  • 1
    yup, you got it right `\x27` instead of `'` ... I actually didn't bother with it after seeing error, or I would have based my answer just on that... – Sundeep Mar 22 '17 at 16:53
1

You can use double quotes to protect the single quotes, like so:

echo "like 'aaa' aaa" | sed "s/like\( '[^']*'\)/like\U\1/g"

Outputs:

like 'AAA' aaa
codeforester
  • 39,467
  • 16
  • 112
  • 140