I had a customer submit a batch file to be processed in one of our systems and they handed me an Excel file with short dates (mm/dd/yy). Sheesh, this is 2007, can we not get full 4 digit years, please!
so, I wanted to work out a method for quickly converting those date strings within mysql
assume a table of:
- BirthDate char(16)
- BDMonth tinyint
- BDDay tinyint
- BDYear smallint
UPDATE MyDatesTable
BDMonth = SUBSTRING_INDEX(BirthDate, ‘/’, 1),
BDDay = SUBSTRING_INDEX( SUBSTRING_INDEX(BirthDate, ‘/’, 2 ) , ‘/’, -1 ),
BDYear = SUBSTRING_INDEX(BirthDate, ‘/’, -1)+1900,
UPDATE MyDatesTable
SET BirthDate = CONCAT(BDYear, ‘-’, BDMonth, ‘-’, BDDay)
ALTER TABLE MyDatesTable MyDatesTable MyDatesTable datetime;
2 digits years are SOOO last century… 
Posted by Brian Blood as Data Translation, Database, MySQL, Text Munging at 3:00 PM CDT
No Comments »
We are trying very hard to move all our systems to PHP 5. This means going through lots of old code and correcting some bad habits.
The biggest offender is the not quoting of references to keys in an associative array like so:
$Data[FirstName]
which should be:
$Data['FirstName']
so, I pulled out my favorite text munger, BBEdit and it’s excellent grep functionality and the ability to do searching over a directory. I ended up using this pattern:
\[([a-zA-Z]+[_a-zA-Z0-9]+)\]([^"'}])
This is looking for a left bracket, then any string that must start with an alpha character, then a right bracket. It’s also making sure there is NOT a quote or tick or right brace after that. The replacement pattern of:
['\1']\2
Now, this search picks up more than one would want, so it does take some effort to manually go through the results and do the replacement one by one. But I was able to take a medium to large code base and clean it up in about an hour.
Posted by Brian Blood as Text Munging, Web App Development at 5:12 PM CDT
No Comments »