Digital Media, Web Design, and Web Development by Ethan Kent | ethanmultimedia.com

ethan -at- ethanmultimedia -dot- com
six one nine - two four six - four one two zero

REGEX Notes

– 2008-04-28 17:43

Recently I used MySQL Query Browser for some data import. I had a couple of spreadsheets and some word documents. It’s really not so tough to import data into a database, but I’d like a lightning fast GUI that opens CSV files and creates DB tables to match. Unfortunately, the MySQL GUI Tools don’t provide this convenience. I did, however run across the RegEx Text Importer and used it to parse a couple of text lists. I don’t do a ton of work with regular expressions. So I like to use the most obvious notation and keep notes.

Example 1

A List of Tab Separated Values

1	Owner	 
2	President	
3	Manager	
4	Executive Director 
5	Principal	
6	Publisher	
7	Administrator
8	Religious Leader
9	Partner	
A	Chairman	
B	Vice Chairman
C	Chief Executive Officer (CEO)
D	Director	
E	Chief Operating Officer (COO)
F	Chief Financial Officer (CFO) 
G	Treasurer 	
H	Controller	
I	Executive Vice President
J	Senior Vice President
K	Vice President
L	Administration Executive
M	Corporate Communications Executive
N	IT Executive
O	Finance Executive
P	Human Resources Executive 
Q	Telecommunications Executive
R	Marketing Executive
S	Operations Executive
T	Sales Executive
U	Corporate Secretary
V	General Counsel
W	Executive Officer
X	Plant Manager
Y	Purchasing Agent
Z	Auditor	

The Regular Expression

([0-9A-Z]{1})\t(\w.([^\t*\r*\n*]*))

How It Works

([0-9A-Z]{1})\t is the first part of the expression. That tells the REGEX engine to find a single number or uppercase letter followed by a tab. Then assigns the value of that character to $1.

(\w.([^\t*\r*\n*]*)) is the second part of the expression. It tells the REGEX engine to start at the next letter, number, or underscore and follow it to the end of the field (signified by \t) or the end of the line (signified by \r or \n). The value of that string is then assigned to $2.

Generating The SQL Statement

The insert wrapper defaults to:

INSERT INTO foo()
VALUES();

To get the values in there change the statement to something like:

INSERT INTO mytable(id, description)
VALUES('', '');

Then drag the $1 and $2 entries in the Parse Structure pane down to their respective places between the parentheses. The result should look like:

INSERT INTO mytable(id, description)
VALUES('$RegEx1.1', '$RegEx1.2');

Example 2

One Identifier and a Number Range

A	1	-	24
B	25	-	49
C	50	-	99
D	100	-	299
E	300	-	499
F	500	-	999

The Regular Expression

([A-Z]{1})(\t)([0-9]*)(\t)(.{1})(\t)([0-9]*)

The SQL Wrapper

INSERT INTO mytable(id, description)
VALUES('$RegEx1.1', '$RegEx1.3 - $RegEx1.7');

Conclusion

All in all, not the speediest technique I’ve ever seen. I do like the RegEx tool though. It’s the type of tool that I like to have lying around, just in case…

MySQL Workbench 5.0 OSS looks like a great planning tool. I look forward to writing up an example on that soon.

---

Filed Under: code sample, mysql, regex, regular expressions

---

Comment

1. While this subject can be very touchy for most people, my opinion is that there has to be a middle or common ground that we all can find. I do appreciate that youve added relevant and intelligent commentary here though. Thank you!

   — Resume Writing Service · 2010-07-27 07:00 · #

2. This was some of the best information that I got from your blog.

   — Research Term Papers · 2010-08-19 01:41 · #

3. Having read this material, I have learned for myself a lot of the new. Thanks

   — college board · 2011-05-21 10:54 · #

Name		Remember
E-mail
Website
Message
	Textile Help

« How To Get Textpattern To Open Links In A New Window « Previous Article

Next Article » How To Select An Incrementing Column With MySQL »