Architect, Developer, Web Geek!

December 3, 2015
by daharveyjr


DB2 REPLACE Function

The DB2 REPLACE function replaces all occurrences of a search string within a source string. If the search string is not found within the source string, the original string is returned unchanged.

The syntax for the REPLACE function is shown here:

SELECT REPLACE({source_string}, {search_string}, {replace_string});

-- After DB2 v9.7 Fix Pack 5 {replace_string} is optional
SELECT REPLACE({source_string}, {search_string});

To show an example statement in use:

-- Leveraging a table column

The above query will take the source string, defined as a field, or explicit string, in our case the LONGDESCRIPTION value from CATENTDESC and search it for the search string ‘A’. All instances of ‘A’ will then be replaced ‘B’.

NOTE: There are few things to consider regarding the REPLACE function:

  1. If the source, search, or replace string is not a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type it will be it will be cast to a VARCHAR data type before evaluation.
  2. Staring in DB2 Version 9.7 Fix Pack 5 and later, the replace string is optional as noted in the above examples. When the expression is an empty string or the replace string has not been specified, nothing will replace the string that is removed from the source string.

July 21, 2015
by daharveyjr

Portfolio Project: Fit Uniforms


About the Project

Fit Uniforms was running an e-commerce website based on WordPress and Jigoshop that was in need of some help. They came to me wanting to optimize and redesign their website. They wanted a website that was professional, easy to navigate, and mobile friendly. A website that allowed mobile devices to easily complete a purchase. In addition, they also wanted to move to a new hosting provider, one that provided better features and more stability.

Keeping them based on their existing WordPress and Jigoshop platform, a new professional looking and mobile responsive website theme was built and customized for them, allowing their customers to interact and shop with them regardless of the device they were using. Mobile checkout was a high priority during the redesign, and a lot of testing was done throughout the project to ensure the flow worked, on both a desktop and mobile device. Their web site content was enhanced, and additional content, galleries, and product portfolios were added allowing them to showcase their uniform work to prospective customers. In addition, there were moved to a new hosting provider and setup with a plan accurately sized to their traffic and business needs.

View Portfolio Page | Visit Fit Uniforms

February 17, 2015
by daharveyjr

Portfolio Project: MRP Design Engineering

MRP Design Engineering

About the Project

I recently completed work on a new project for MRP Design Engineering. We took their existing website, built using the SnippetMaster web editor, and migrated it to the WordPress platform with a new look and feel. Doing this allows them to easily control and manage their own content, which is an option they really didn’t have before. In addition, the website features an updated and improved user interface, enriched content across across the services offered to clients, a new navigation layout that is well-defined and structured, and resolves a few outstanding bugs that limited their client interactions on the old website.

View Portfolio Page | Visit MRP Design Engineering

December 22, 2014
by daharveyjr


MySQL REGEXP (Regular Expression) Operators

MySQL offers the ability to use regular expressions to perform complex searches against your data. A regular expression is a tool that provides for a concise and flexible way to identify strings of text based on user-defined patterns.

This article will discuss the MySQL regular expression operators, review their use and syntax, and identify the constructs and special characters that can be used in a MySQL regular expression, as well as provide a few examples of their use.

MySQL Regular Expression Operators

The following operators are used in MySQL to perform regular expression operations. These are used in a WHERE clause similar to the well-known and often used LIKE operator.

  • REGEXP: The pattern matching operator for using regular expressions.
  • NOT REGEXP: The negation of the REGEXP operator.
  • RLIKE: A synonym for the REGEXP operator.

MySQL Regular Expression Syntax

The basic syntax used for MySQL regular expression operations is:

-- For the REGEXP Operator

-- For the NOT REGEXP Operator

-- For the RLIKE Alias Operator

To provide more detailed, yet simple, example of a MySQL regular expression operation, take the following statement. It will retrieve all the columns of each record in the table PRICE where the PRICELIST_ID matches the pattern specified (starts with the numeric range 0-9 occurring one or more times, followed by an ‘_’ (underscore), and then the character sequence ‘USD’.


Another example of a MySQL regular expression operation, can be shown in the following statement. It will retrieve all columns of each record from the PRICE table where the PRICE_ID matches the pattern specified (starts with an O, followed by and ‘_’ (underscore), then the numeric range 0-9 occurring one or more times, followed by and ‘_’ (underscore), then ending with either the character sequence USD, or BRA.


MySQL REGEXP Constructs and Special Characters

A MySQL regular expression may use any of the following constructs and special characters to construct a pattern for use with the REGEXP operators. The construct or special character is shown, followed by a description of each and what operations in performs within the pattern for the regular expression.

  • ^ : Match the beginning of a string.
  • $ : Match the end of a string.
  • . : Match any character (including carriage return and newline characters).
  • a* : Match any sequence of zero or more a characters.
  • a+ : Match any sequence of one or more a characters.
  • a? : Match either zero or one a characters.
  • de|abc : Match either of the character sequences, de or abc.
  • (abc)* : Match zero or more instances of the character sequence abc.
  • {1},{2,3} : Provides a more general way of writing regular expressions that match many occurences of the previous atom (or “piece”) of the pattern. i.e. a? can be written as a{0,1}.
  • [a-dX],[^a-dX] : Matches any character that is (or is not, if ^ is used) either a, b, c, d, or X. A “-” character between two other characters forms a range that maches all characters from the first character to the second.
  • [.characters.] : Within a bracket expression (using “[” and “]”), matches the sequence of characters of that collating element. i.e. the pattern [[.period.]] would match the ‘.’ (period) character.
  • [=character_class=] : Within a bracket expression, represents an equivalence class. It matches all characters with the same collation value, including itself.
  • [:character_class:] : Within a bracket expression, represents a character class that matches all characters belonging to that class. i.e. the pattern [[:alpha:]] would match against a string that is all aphabetic characters.
  • [[:<:]],[[:>:]] : These markers stand for word boundaries, and as such they match the beginning and ending of words, respectively.

* NOTE: MySQL interprets the “\” (backslash) character as an escape character. If you choose to use the “\” character as part of your pattern in a regular expression it will need to escaped with another backslash “\\”.

For further documentation on the MySQL regular expression operator, please visit Regular Expressions in the MySQL Reference Manual (v5.1 currently linked).

December 15, 2014
by daharveyjr


Listing Subcategories on a Category Page in Magento

I recently was working on a Magento Commerce website for a client that wanted to display all of the subcategories of a category on the category page, to improve basic navigation, and to further enhance and show the range of products that they carried.

The following code snippet was used to pull the child categories from the category object on the category page, and retrieve the necessary information for display. It retrieves the child categories from the category object (in the form of a comma-delimited string) using the getChildren() method, explodes them into an array, and iterates over that array, retrieving the category object for each subcategory ID identified. If the subcategory is marked as an acive category, the category name, thumbnail, and URL are then retrieved and displayed on the page as needed.

NOTE: The client’s HTML structure and code has been removed from the snippet; however, the code snippet still provides an appropriate example to retrieve and display child categories.

$_subcategories = $_category->getChildren();
if ($_subcategories && trim($_subcategories) != '') {
	$_subcategory_ids = explode(',', $_subcategories);
	foreach($_subcategory_ids as $_subcategory_id): ?>
		<?php $_subcategory = Mage::getModel('catalog/category')->load($_subcategory_id); ?>
		if ($_subcategory->getIsActive()): ?>
			<?php if ($_subcategory->getThumbnail()): ?>
				<a href="<?php echo $_subcategory->getUrl(); ?>"><img src="<?php echo $_subcategory->getThumbnail(); ?>" /></a>
			<?php endif; ?>
			<a href="<?php echo $_subcategory->getUrl(); ?>"><?php echo $_subcategory->getName(); ?></a>