Simple Way to Get a Domain from an Email Using T-SQL

One of my recent projects required me to create a Microsoft SQL Server view that displayed (among other information) and email address and its underlying domain. Microsoft SQL Server and T-SQL provide a few simple functions that, when used together, make this a fairly easy task.

So here’s the T-SQL example (with the important stuff highlighted):

SELECT EMAIL,
RIGHT(EMAIL, LEN(EMAIL) - CHARINDEX('@', EMAIL)) AS 'DOMAIN' 
FROM MYTABLE

Parsing text inside a SQL Query can slow things down if you’re dealing with a large dataset, so use this code snippet with caution. If the application allows it, it’s generally a better idea to perform this kind of parsing in the application layer. ColdFusion, PHP and JavaScript provide comparable functions to T-SQL’s RIGHT, LEN and CHARINDEX.

If you’re feeling brave, you can also try pure RegEx rather than use native functions.

Replacing Long Strings In SQL Exports and Other Large Text Files

I’m migrating a web forum from one MySQL server to another, and it’s usually a straight-forward task consisting of exporting the data from one server importing an other server using phpMyAdmin.  This forum has been particularly active, so the SQL dump file containing the discussion table is almost two gigabytes. This wouldn’t be such a big deal if I were just importing into an identical database, but “business requirements” dictate that I change the name of the database.

Normally I would use Find/Replace in Dreamweaver, but being the bloated application it is, it kept throwing “out of memory” errors at me each time it hit the discussion export file. I suspected Notepad++ wouldn’t fare much better, so I started to Google for a solution.

A lot of options came up for Linux and Perl (which has always been awesome at parsing text), but I really didn’t want to have to move the file off my Windows 7 workstation over to the LAMP virtual server and just add more complexity to the confusion.

So I hit Google again with with a modified search (I just added Windows). I popped open a few tabs and browsed through a couple of options that would require yet-another GUI program installed on my machine. Some looked promising, but shareware always comes with a price, so I started digging into some of the more “hardcore” alternatives.

StackOverflow is one of my absolution favorite developer resources, and when I saw the link to “How can you find and replace text in a file using the Windows command-line environment?“, I knew my chances were good for finding a solution. StackOverflow contributor Mile Schall proposed a solution using Windows Power Shell, which proved to work extremely well.


First, anyone who isn’t familiar with Windows PowerShell should get familiar fast. It’s major upgrade over the old command prompt, gives access to some nifty .NET functionality, and will generally make life easier for anyone working with Windows Servers and Clients.

Following the StackOverflow advice, I launched PowerShell on my Windows 7 machine by going to:

START > All Programs > Accessories > Power Shell > Power Shell

I then navigated to the directory containing my SQL dump file, and entered the following command (of course the target text strings and file names have been changed for privacy):

   Get-Content SQLexport.sql | ForEach-Object { $_ -replace "replace this string", "with this string" } | Set-Content SQLexport2.sql

After a few minutes, the PowerShell prompt returned a new “SQLexport2.sql” file appeared in the directory.

This snippet has worked great for my large SQL files, and I suspect it will also work well for other large text-based files.

Now, the next trick is to get by the 2 MB file limit in phpMyAdmin so I can import the updated 2 gigabyte SQL dump file back into MySQL.

Formatting PHP Date() Time Strings with AP Style

While developing an event management application, I ran into an interesting challenge: we use Associated Press (AP) Style for our Web publications, but PHP’s date() function only support time suffixes “am / pm” or “AM / PM”. AP Style calls for times to be formatted with “a.m. / p.m.”

To solve the problem, I came up with the following function:

CODE

<?php

function APtime($timestring){

$APstyleTime = (str_replace(array(“am”,”pm”,”AM”,”PM”), array(“a.m.”, “p.m.”,”a.m.”, “p.m.”), $timestring));
return $APstyleTime;
}

$myTime = ’12:04 pm’;

echo APtime(date(“g:i a”, strtotime(“$myTime”)));

?>

 

OUTPUT

12:04 p.m.

I’m not a big fan of PHP’s support of date/time formats and masks, but at least this is a simple (and reusable) solution to an annoying problem.

 

Five Tips to Jump-start Vanilla Forums Theme Development

Quite a bit of my work over the past year has been integrating ListServ (the grand-daddy of email discussion lists) with a modern web forum. I built the initial prototype from the ground up, but performance issues and lack of bells and whistles most users expect these days led me to evaluate several open source platform. After some research, a couple of false starts, and one near-disaster, I settled on Vanilla Forums.

Vanilla Forums is a mature open source forum platform with an active community in which the developers actively participate and listen to the ideas, wishes and rants of its community members. The Vanilla team has been working hard to develop formal Documentation for the platform, and while I would consider the forum administrator and user documentation complete, the developer documentation is only half way there.

I think part of the challenge in documenting Vanilla Forums stems from the developers us of Model-View-Controller (MVC) design pattern. MVC helps developers produce high-performance, reusable code, but it’s easy for an a developer unfamiliar with working with Object Oriented MVC code to get lost in layers of abstraction.

I found myself in this situation as I neared completion of my ListServ integration project. Much of my work over the past decade has been refactoring spaghetti code into some semblance of structure, and I’ve had a good bit of experience working with object oriented PHP, but I’ve never seen anyone drink the MVC Kool-Aid like the developers at Vanilla. Their code is tight, clean and performs extremely well, but it isn’t always clear what classes, methods and functions to use.

In future posts, I’ll try to dig down deeper in how to make Vanilla Forums behave, but for now, I’ll share my quick and dirty tips on where developers can get started building their own Vanilla Forums Themes.

Take advantage of Open Source themes.
If you’re building your own theme, don’t. Steal, borrow, or exercise some Open Source license on a theme you like that’s structurally close to what you want to do. Use Find/Replace (at least in Dreamweaver) to change all instances of the theme name. And don’t forget to rename any files that contain the them name. Oh, don’t forget to give yourself credit for your new awesome theme in the “about.php”.
Don’t be a Smarty-pants.
Smarty is a templating engine built to run on top of a PHP application. Unfortunately, Vanilla has only implemented a few “Smarty tags”, so using the Smarty theme templates (you can recognize them by their .tpl extension) is a bit painful for PHP developers familiar with using PHP at the presentation layer. To prove you’re smarter than Smarty, and to make building your own custom theme significantly less frustrating, delete “deafault.master.tpl” from your theme’s “views” directory and replace it with a copy of default.master.php (found in the “/[your-vanilla-root]/applications/dashboard/views” directory)
Do it with Style(s).
Vanilla’s stylesheet “strategy” can get messy fast. Create a “custom.css” in the “[your-theme]/design/” directory. As you modify classes, copy the whole class you want to use from “/[your-vanilla-root]/applications/dashboard/design/style.css”. You can often achieve everything you want out of a custom template just by modifying the stylesheet.
Get plugged in.
Plugins are relatively easy to use in Vanilla. If you want some special functionality out of your theme, look for a plugin that does something similar, and then make it your own. If you can, try to take advantage of Vanilla’s built-in modules and classes, but if you’re trying to build a simple plugin on a tight deadline, sometimes it’s easier just use ten lines of PHP to query the database and echo the results where your want them, rather than using a few hundred lines across several files to build to MVC design patterns.
Do whatever it takes.
If you can’t figure out where Vanilla is adding a particular element (usually it’s embedded deep within a module, inside of an enigma, wrapped in a mystery), don’t be afraid to commit CSS assassination (visibility: hidden !important). Just remember to come back to it once you’ve done your product demo.

Hardcore Vanilla developers will probably flame me over these five tips, but sometimes it’s better to be a pragmatist rather than a perfectionist.

After all, if the client is happy with the job, then it’s a job well done.