July 09, 2007 WEBDEV: Convert an option list to SQL with TextMate and Regular Expressions 
I grabbed a list of auto manufacturers from another site’s dropdown menu:
<option value="Acura" >Acura</option>
<option value="Alfa Romeo" >Alfa Romeo</option>
<option value="American Motors" >American Motors</option>
<option value="Audi" >Audi</option>
<option value="BMW" >BMW</option>
...
With the intention of converting it to SQL for my database.
Replacing the first portion of each item is easy: Do a Find & Replace on:
<option value=”
replacing it with
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, '
The second half requires a bit more than just Find & Replace, though, as each string is different. Fortunately TextMate supports Regular Expressions. The second portion is a Find & Replace on:
" >.*<\/option>
replacing it with
', NOW(), NOW());
leaving us with
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, 'Acura', NOW(), NOW());
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, 'Alfa Romeo', NOW(), NOW());
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, 'American Motors', NOW(), NOW());
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, 'Audi', NOW(), NOW());
INSERT INTO Answer (QuestionId, Text, CreatedDate, ModifiedDate) VALUES (2, 'BMW', NOW(), NOW());
...
Regular Expression string explained:
" >
is the end of the option tag.
.*
is any character (the .) repeated (the *) any number of times, until we hit
</option>
with the \ to escape the / character.
I grabbed my VisiBone Browser Book to look up syntax. In the past I’ve also used http://www.regular-expressions.info/reference.html and realtime JavaScript RegEx evaluators such as this one: http://www.teria.com/~koseki/memo/javascript/realtime_eval.html. Keep in mind, though, that a JavaScript evaluator is evaluating RegEx in JavaScript, which may be more or less robust and/or slightly syntactically different than your language of choice.
