3

I have created a line of text that looks like this:

INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,,00:23:32:c2:a9:e8,Auth-Type,:=,Accept);

I want to use sed to make it look like this:

INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');

This makes way more sense in context and I have gotten a little farther with it over the last (apparently) 17 hours:

#!/bin/bash
ssh ubnt@xxx.xxx.xxx.xxx brmacs >>MACS.txt mv MACS.txt /etc/persistent scp mhalls@xxx.xxx.xxx.xxx:/etc/persistent/MACS.txt MACS.txt
sed -i "1d" MACS.txt
head -c 58 MACS.txt >>shortmacs.txt
tail -c 18 shortmacs.txt >>usermac.txt
sed 's/"//g' usermac.txt >>usermacrdy.txt
sed -i 's/^/INSERT INTO `radcheck`(`id`, `username`, `attribute`, `op`, `value`) VALUES (,'',/' usermacrdy.txt
sed "s/$/','Auth-Type',':=','Accept');/" usermacrdy.txt > sqlquery.txt
sed -i "s/,,/\,\'\',\'/" sqlquery.txt
rm -f MACS.txt
rm -f shortmacs.txt
rm -f usermac.txt
rm -f usermacrdy.txt

WORKS!!!

The head and tail cut the MAC address out of the original text file xfer'd over from the UBNT CPE device and then I pass it through sed to build the SQL syntax around the MAC address.

After all that I found out the id portion of the query is not needed for success so now I am in slightly the same boat with:

sed -i 's/^/INSERT INTO `radcheck`(`username`, `attribute`, `op`, `value`) VALUES (/' usermacrdy.txt
sed "s/$/','Auth-Type',':=','Accept');/" usermacrdy.txt > sqlquery.txt
sed -i "s/\,\'\',\'//" sqlquery.txt
9

There are four ways to include the single quote that you need.

One cannot escape a single-quotes string within a single-quoted string. However, one can end the quoted string, insert an escaped single-quote, and then start a new single-quoted string. Thus, to put a single quote in the middle of 'ab', use: 'a'\''b'. Or, using the sed command that you need:

$ sed -r 's/,([^ ),]+)/,'\''\1'\''/g; s/,,/,'\'\'',/g' file
INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');

The second way is to use a double-quoted string, in which case the single-quote can be inserted easily:

$ sed -r "s/,([^ ),]+)/,'\1'/g; s/,,/,'',/g" file
INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');

This issue with double-quoted strings is that the shell does processing on them. Here, though, there are no shell-active characters, so it is easy.

The third method is to use a hex escape as PM2Ring demonstrates.

The fourth way, suggested in the comments by Jonathan Leffler, is to place the sed commands in a separate file:

$ cat script.sed 
s/,([^ ),]+)/,'\1'/g
s/,,/,'',/g
$ sed -rf script.sed file
INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');

This way has the strong advantage that sed reads the commands directly without any interference from the shell. Consequently, this completely avoids the need to escape shell-active characters and allows the commands to be entered in pure sed syntax.

How the sed solution works

The trick is to put single quotes around the comma-separated strings that you want but not around the others. Based on the single example that you gave, here is one approach:

  • s/,([^ ),]+)/,'\1'/g

    This looks for one or more non-space, non-comma, and non-close-parens characters which follow a comma. These characters are placed inside single quotes.

  • s/,,/,'',/g

    This looks for consecutive commas and places a two single-quotes between them.

OSX and other BSD platforms

To avoid extra backslashes, the above sed expressions use extended regular expressions. With GNU, these are invoked as -r but, with BSD, they are invoked with -E. Also, some non-GNU sed do not accept multiple commands separated with semicolons. Thus, on OSX, try:

sed -E -e "s/,([^ ),]+)/,'\1'/g" -e "s/,,/,'',/g" file

Addendum: Matching a MAC address

From the comments, we have the following input;

$ cat file3
 INSERT INTO radcheck(username, attribute, op, value) VALUES (00:23:32:c2:a9:e8,'Auth-Type',':=','Accept'); 

And, we want to put single-quotes around the MAC address that follows the open-parens. To do that:

$ sed -r "s/\(([[:xdigit:]:]+)/('\1'/" file3
 INSERT INTO radcheck(username, attribute, op, value) VALUES ('00:23:32:c2:a9:e8','Auth-Type',':=','Accept'); 

In any locale, [:xdigit:] will match any hexadecimal digit. Thus, ([[:xdigit:]:]+) will match a MAC address (hex digit or colon).

  • 1
    One other option is to put the command in a file and use sed -f sed.script … to run it. This sometimes makes life easier. You can use a here-doc with quoted delimiter (cat >sed.script <<'EOF', etc) to create the script without needing to escape any quotes, etc. – Jonathan Leffler Jan 3 '15 at 8:45
  • what if values in sequence two contain spaces? – mikeserv Jan 3 '15 at 11:55
  • @JonathanLeffler Excellent idea: I have updated the answer. – John1024 Jan 3 '15 at 21:13
  • @John1024 I think - $ s/,([^ ),]+)/,'\1'/g is a great way to look at it. I have done a lot more work on the script and the thing is the MAC address is considered the entire contents of the original input file so all the seding builds that other text around it. I have gotten the following to work: – Michael Conrad Halls Jan 4 '15 at 1:25
  • 1
    100% SOLVED all the way to sql – Michael Conrad Halls Jan 4 '15 at 5:24
2

When you work globally you've got to consider how sed reads. A global substitution is going to divide up a pattern space into individual fields delimited per your specifications and operate on each. The delimiters are recognized from left to right - in the order they are read - and sed will apply each action as soon as it might. Here is a single s///ubstitution statement that can do what you're asking:

sed "s/\(([^)]*)[^,]*\)\{0,1\},\([^,)]*\)/\1,'\2'/g
" <<\IN
INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,,00:23:32:c2:a9:e8,Auth-Type,:=,Accept);

When run, that prints:

INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');

Here's how:

  • \(([^)]*)[^,]*\)\{0,1\} - Most important are the distribution of *zero+ matches and the other kind. This entire block is optional - sed will happily match a null string in its place given the opportunity - and it actually does a lot of the time, else this wouldn't work at all. But because \{0,1\} is used sed will not match more than one entire sequence like this. And that's why the distribution is important. Within this block we have:
    • ( - at least one left paren.
    • [^)]* - zero+ not-right parens.
    • ) - one right paren
    • [^,]* - zero+ not-commas.
  • sed will match each of these in order - and as a block because they are \(grouped\). The *zero+ match is very important globally - if you use + at least one in that setting you drastically reduce effectiveness of your applied edits. For just the input the block above, sed delimits your string thus:

  • INSERT INTO radcheck - This string comes before any of our matches and contains none of them. It is ignored.

  • (id, username, attribute, op, value) VALUES ( - This string makes up our entire * optional match block. First sed matches the ( then the whole string of [^)]* then the ) and last - and importantly - the whole string of [^,]* which carries us beyond another match case for (. It cannot now match - and so skip - the next ( for this optional match group. sed will try the next instead.

And while sed applies the replacements for \2 on the second (.,.,.) sequence it will still apply replacements for \1 as well because it still matches \1. I first matched \1 as the entire (.,.,.)..( block, saved it in \1 and replaced it with \1 - with itself. From the beginning of the second sequence up to the next occurring ( sed will replace \1 with null string every time it matches one - which is every time it matches a comma.

Given many sequences like this on a line - and given that commas only occur within the ( ) delimiters, sed will alternate quoting for each sequence. Here's your sample several times:

INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');INSERT INTO radcheck(id, username, attribute, op, value) VALUES (,'','00:23:32:c2:a9:e8','Auth-Type',':=','Accept');
  • ,\([^,)]*\) - notice that the only not optional match for the entire regex is the ,comma. The other block occurs before it in the expression and in input and so sed allows it to apply - but the comma takes precedence when all else is equal, and it is the only reason sed does not insert ''s everywhere as it matches all of the null-strings between every byte on the other two blocks do not match a null-string while applying - sed delimits on it.
  • sed will continue to match \2 - and save to it - each sequence of chars that are not ) and not ,. It will replace each comma with it encounters with ,'\2'. It will continue in this fashion until it encounters ( at which point it will be able to fill \1 and so will do so.
  • OK so RIGHT HERE! "(id, username, attribute, op, value) VALUES ( - This string makes up our entire * optional match block. First sed matches the ( then the whole string of [^)]* then the ) and last - and importantly - the whole string of [^,]* which carries us beyond another match case for (. It cannot now match - and so skip - the next ( for this optional match group. sed will try the next instead." I now need to add one more ' after the next ( – Michael Conrad Halls Jan 4 '15 at 4:35
1

Try this:

sed 's/,,/,\x27\x27,/'

\x27 is the hex escape code for '

demo

$ echo 'INSERT INTO `radcheck`(`id`, `username`, `attribute`, `op`, `value`) VALUES (,,00:23:32:c2:a9:e8,Auth-Type,:=,Accept);' |
> sed 's/,,/,\x27\x27,/'
INSERT INTO `radcheck`(`id`, `username`, `attribute`, `op`, `value`) VALUES (,'',00:23:32:c2:a9:e8,Auth-Type,:=,Accept);
  • You are missing the quotes around the other strings in that second block... – jasonwryan Jan 3 '15 at 7:32
  • @jasonwryan: I didn't notice those... oops! But I guess I'll leave my answer as it is, since John1024 seems to have it covered nicely. – PM 2Ring Jan 3 '15 at 8:30
  • +1 for concisely answering the question that the OP specifically asked. – John1024 Jan 3 '15 at 20:32

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.