regex to split name=value,* into csv of name,* and value,*

Question

I would like to split a line such as:

name1=value1,name2=value2, .....,namen=valuen

two produce two lines as follows:

name1,name2, .....,namen
value1,value2, .....,valuen

the goal being to construct an sql insert along the lines of:

input="name1=value1,name2=value2, .....,namen=valuen"
namescsv=$( echo $input | sed 's/=[^,]*//g' )
valuescsv=$( echo $input | ?????? )

INSERT INTO table_name ( $namescsv ) VALUES ( $valuescsv )

Id like to do this as simply as possible - perl awk, or multiple piping to tr cut etc seems too complicated. Given the names part seems simple enough I figure there must be something similar for values but cant work it out.

Nekresh · Accepted Answer · 2011-01-28 15:06:00Z

3

You can just inverse your character match :

echo $input | sed 's/[^,]*=//g'

answered Jan 28, 2011 at 15:06

Nekresh

2,99825 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hanlonj Over a year ago

fantastic. I was trying $input | sed 's/,[^=]*//g' , new it had to be someting close. knew I should have signed up to this site years ago

jens_profile · Accepted Answer · 2011-01-28 15:05:06Z

0

i think your best bet is still sed -re s/[^=,]*=([^,]*)/\1/g though I guess the input would have match your table exactly.

answered Jan 28, 2011 at 15:05

jens_profile

3614 silver badges12 bronze badges

2 Comments

hanlonj Over a year ago

could you clarify why this would be better? my regex eyes are mediocre

jens_profile Over a year ago

ah no, the one above is just as good, although maybe a tad less rigorous.

Arnaud Le Blanc · Accepted Answer · 2011-01-28 15:18:22Z

0

Note that in some RDBMS you can use the following syntax:

INSERT INTO table_name SET name=value, name2=value2, ...;

http://dev.mysql.com/doc/refman/5.5/en/insert.html

The following shell script does what you are asking for and takes care of escaping (not only because of injection, but you may want to insert values with quotes in them):

_IFS="$IFS"; IFS=","
line="name1=value1,name2=value2,namen=valuen";

for pair in $line; do
        names="$names,${pair%=*}"
        values="$values,'$(escape_sql "${pair#*=}")'"
done
IFS="$_IFS"

echo "INSERT INTO table_name ( ${names#,} ) VALUES ( ${values#,} )"

Output:

INSERT INTO table_name ( name1,name2,namen ) VALUES ( 'value1','value2','valuen' )

edited Jan 28, 2011 at 15:18

answered Jan 28, 2011 at 15:09

Arnaud Le Blanc

100k24 gold badges211 silver badges196 bronze badges

1 Comment

hanlonj Over a year ago

thanks, good to know. however doesn't seem to be the case with sqlite3 that I am using for this.

Collectives™ on Stack Overflow

regex to split name=value,* into csv of name,* and value,*

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related