The Problem: A column in a MySQL table stored information as a concatenated string. I needed an easy way to split the string, analogous to PHP’s “explode” command that splits a string into parts based on a given delimiter.
For example, given this string and a pipe ( | ) as the delimiter,
New Age | Spacious | Floating | Space | Drifting | Dream | Strings | Vocals | Percussion |
I needed this result,
New Age as keyword1, Spacious as keyword 2, and so on.
Solution One: If you have root access to MySQL, you can create an UDF (User Defined Function), such as that below.
CREATE FUNCTION SPLIT_STR( x VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '');
A query that uses the UDF looks like this:
SELECT keywords, strSplit(keywords, '|', 1) as keyword1, strSplit(keywords, '|', 2) as keyword2, strSplit(keywords, '|', 3) as keyword3, strSplit(keywords, '|', 4) as keyword4, strSplit(keywords, '|', 5) as keyword5, strSplit(keywords, '|', 6) as keyword6, strSplit(keywords, '|', 7) as keyword7, strSplit(keywords, '|', 8) as keyword8, strSplit(keywords, '|', 9) as keyword9, strSplit(keywords, '|', 10) as keyword10 FROM Tracks2
There’s one problem. If you don’t have the administrative rights to create the UDF, which is usually the case on a shared host, you can’t use this solution.
1. How can I send a million frackin’ letters about hydraulic fracking?
A non-profit organization (A Million Fracking Letters) wanted to send a million letters to the governor’s office concerning the issue of hydraulic fracturing.
Solution: We created an online custom letter writer that allows a website visitor to choose from “canned” text or add their own text to a preformatted letter. All the website visitor need do is add some basic contact information and click a few buttons. The result? Thousands and thousands of letters are quickly and easily generated.
2. How can a user search through one hundred and fifty issues of a print magazine?
A non-profit organization (Teachers & Writers Collaborative) wanted to make their print publication accessible on the web.
Solution: We digitized all of their publications and created a searchable database. Now a website visitor can find any article, no matter when it was printed, and easily download and print a copy of that article…for free!
3. How can I search through 15,000 music tracks?
Omnimusic has been providing music tracks for use in commercials, television and cable specials, and anywhere professionally produced music is needed. The problem is that they have over 15,000 music tracks. How can you search through all of those music tracks?
Solution: We created a customized search engine that allows a website visitor to search through music tracks using hundreds of possible preconfigured keywords. After a search has been made, a website visitor can refine the results even further.
The Client: The Teachers & Writers Collaborative (TWC) was founded in 1967 by a group of writers and educators who believed that professional writers could make a unique contribution to the teaching of writing and literature. They publish a quarterly magazine that features a mix of interviews; essays; exercises for the writing classroom; how-to pieces on teaching the work of particular writers, genres, and forms; and explorations of the writing life.
The Problem: TWC wanted to convert their magazine into a searchable form that would allow educators to find articles that could be used in the classroom.
The Solution: We developed an online version of the quarterly magazine (approximately 150 issues). A user can search for a magazine by category, by data source (additional data sources will be added in the future), and, if desired, a search string. The result allows a user to view a plain text version of the article and, if desired, a PDF version of the article that can be viewed, printed and saved.
You can see it in action at the Teachers & Writers Collaborative Digital Resource Center.
Some links of interest:
- A list of all Excel functions broken down by category. Each function is hyperlinked to its definition.
- Do you need to rename 100 files on your computer? Here’s a way to do it without getting carpal tunnel syndrome.
- You suspect that your website went down. Is it just your internet connection or did your website really go down? Go to this website to put in your website’s URL and find out if it is down for everyone or just you.
- Instructions on how to clear out your browser’s cache.
Some recently launched websites:
I love my BlackBerry™. I’ve written many posts on my BlackBerry. I’ve replied to many comments and emails from my BlackBerry. But with eight different email accounts all going to my BlackBerry (and Twitter, FaceBook and WordPress for BlackBerry too!), the device can become quite distracting. Each time it beeps when an email arrives, I pick it up, take a look at the email, respond (or not respond) and then go back to my work. This could happen fifty times a day or more. I learned to accept these interruptions as being part of the new era of instant everything.
One day I accidentally left the BlackBerry in “Quiet” mode. Instead of beeping when an email arrived, it would just blink at me with it’s little red light. OMFG! Why didn’t I do this sooner?! I’ve been having very productive days by leaving it in Quiet mode and, every once in a while, like after thirty or more minutes of work when I need to stand up and stretch a bit before sitting back at the computer, I grab the Blackberry and look through the emails while I pace around the home office. Sometimes I will stand and stretch and NOT grab the BlackBerry, so several hours can go by without looking at any emails! You know what? Even though I am not reading and responding to emails the moment they arrive, I have not seen any increase in domestic terrorist activity, any increase in the homicide rate nor any increase in the frequency with which I have sex. (This last one is very disappointing. ) But I feel less stressed. The hurry-up-do-it-now psychosis has noticeably diminished. So I highly recommend silencing your BlackBerry. It will improve your daily work routine. It will make you less stressed. However, it may not improve your sex life.
Copernic created software that makes searching your email, documents, images and most anything else on your computer to be a snap. There’s a freeware version available. The software works unobtrusively as it indexes your files and, unlike Google Desktop Search, which couldn’t handle the massive number of files I have on my drives, Copernic’s software had no problem at all. Click here to check it out.