I have a where clause string in Unix file as below

where c1 in (a) and c2 in (a,b,c) and c3 in ()

Need Output as

where c1 in ('a') and c2 in ('a','b','c') and c3 in ('')

Would be great if some one can complete this.

  • Can you please edit your post to provide sample input and output? It's hard to tell what you're asking exactly. – Wildcard Apr 26 '16 at 21:31
sed -r "s/,/','/g; s/\(([^)]+)\)/('\1')/g"

Let's see how this changes the input you provided:

where c1 in (a) and c2 in (a,b,c) and c3 in ()

First, commas are wrapped in quotes (s/,/','/g):

where c1 in (a) and c2 in (a','b','c) and c3 in ()

Then we add quotes inside non-empty parens (s/\(([^)]+)\)/('\1')/g):

where c1 in ('a') and c2 in ('a','b','c') and c3 in ()

…which is the output you wanted.

Take care, as this regular expression might affect other lines in the ways you don't want.

  • Clever, I was thinking it would have to be done in a loop. – Wildcard Apr 26 '16 at 21:41
  • 2
    @Wildcard - if commas may occur outside those parens then you need to loop. This is something OP should clarify. – don_crissti Apr 26 '16 at 21:44
  • @Alex - Many Thanks, the above solution is working fine, How to edit the same if i want the single quotes when there's no text as well i.e. where c1 in ('a') and c2 in ('a','b','c') and c3 in (''). – sai May 22 '16 at 19:05
  • @sai, just change + to * in the last step. – Alexander Batischev May 23 '16 at 19:51

You can also do it with a loop:

sed -e ':top' -e 's/\([(,]\)\([^),'\'']\{1,\}\)\([),]\)/\1'"'\2'"'\3/;t top'

Or, using -E:

sed -E -e ':top' -e "s/([(,])([^),']+)([),])/\1'\2'\3/;t top"
  • @don_crissti, thanks. I didn't realize it made a difference in portability. It looks like neither is POSIX, but I see that BSD sed supports -E but not -r, while GNU sed supports both. – Wildcard Apr 26 '16 at 22:02
  • 1
    nice, but doesn't work if the input contains other sql code - e.g. echo 'select z, y, x from d, e, f where c1 in (a) and c2 in (a,b,c) and c3 in ()' | sed -E -e ':top' -e "s/([(,])([^),']+)([),])/\1'\2'\3/;t top" produces select z,' y',' x from d',' e',' f where .... The sed script shouldn't touch the select fields or the from tables, or anything else outside of in (...) (and maybe values (...), but that would depend on the field type of the individual values). This is a hard problem to solve, easier to just generate correct SQL in the first place. – cas Apr 27 '16 at 0:18
  • 1
    now that i think about it, the comment about values (...) is also true of the values in an in (...) clause. They should only be quoted if they're varchar or date types or similar, not numeric types. To do it accurately, the script needs to know the data types. – cas Apr 27 '16 at 0:24
  • @cas, I suspect you're overthinking it—as I read it, the Original Poster was just trying to avoid a huge amount of repetitive editing, not trying to automate the whole thing end to end. But you have a good point; if you write an answer that addresses those issues I'd definitely vote for it. :) – Wildcard Apr 27 '16 at 0:28
  • 1
    I don't think i am over-thinking it. I'm speaking (writing) from years of personal experience doing exactly the kind of things that the OP is doing - trying to correct a simple generation of SQL with some fancy post-processing using sed or awk. It turns out that it's actually much easier to write the script so that it generates correct sql code in the first place than to generate almost-correct sql and try to fix it. If the OP reads the comments (i wrote them mostly for him to see, rather than you), he should now be aware of the potential flaws in this approach. – cas Apr 27 '16 at 0:31

If the only thing in your file is a WHERE clause as described in your post, then this should do:

$ sed -r "s/(\(|,)([^ ),]+)(\)|,)/\1'\2'\3/g" <Filename>
where c1 in ('a') and c2 in ('a',b,'c') and c3 in ()

If your file is full of other data, like the rest of that SQL statement, or multiple SQL statements, then this may interfere with other text in that file. Since sed does not have lookbehind/lookahead functionality, you might consider a safer alternative in perl (if your WHERE clauses are on separate lines from the rest of each SQL statement):

$ perl -ne "s/(\(|,)([^ ),]+)(\)|,)/\1'\2'\3/g if /where/; print;" <Filename>
where c1 in ('a') and c2 in ('a',b,'c') and c3 in ()

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.