Data Architecture

Data Manipulation – Splitting Surname and Name

by damonp on June 21, 2007

in Data Architecture,SQL

For a new client project I was provided with database of users consisting of names, email addresses and managers.

CREATE TABLE `users` (
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `manager` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL
)

The name column contained both first and last names formatted last name first like:

lastname, fistname
Parker, Damon

MySQL’s SUBSTRING_INDEX() and TRIM() functions allow us to make quick work of this data manipulation. Using the query below and a new table similar to the first except with separate fields for first and last name we can copy the data to the new table and split the names in the process.

INSERT INTO users_clean (first_name, last_name, email, manager, STATUS) SELECT TRIM(SUBSTRING_INDEX( `name` , ',', 1 )) AS first_name, TRIM(SUBSTRING_INDEX( SUBSTRING_INDEX( `name` , ',', 2 ) , ',', -1 )) AS last_name, email, STATUS
FROM `users`

Popularity: 1%

{ 0 comments }

Statistics With MySQL

by damonp on June 30, 2006

in Data Architecture,Snippets

Need a quick count of repeated occurences of matching rows for statistical analysis?

SELECT search_query, count( search_query ) AS total
FROM search_log
GROUP BY search_query
ORDER BY total DESC

Popularity: 1%

{ 0 comments }

MySQL RAND Function To Randomly Populate Table

20 June 2006

MySQL’s RAND() function can be used to set random values or fill a table with random data. The following SQL snippet will change column1 in all rows to a random number 0-50. UPDATE tablename SET column1 = (rand() * 50)

Read the full article →

DHL Shipping Automation For ZenCart

19 May 2006

DHL EasyShip allows you to easily automate shipping processes. EasyShip allows a user to weigh a package, supply destination information and print a pre-paid package slip. The EasyShip tools communicate with DHL’s servers over the Internet to rate and purchase the service and can notify them a local pickup is ready. The system can also [...]

Read the full article →

Special Imports and Exports For ZenCart

26 April 2006

One of the major weaknesses of an open source package like ZenCart is the lack of support for third party applications. Sure you can insert products by hand and process orders, but what if you already have products in another form such a Point of Sale system or inventory management system? What about integrating the [...]

Read the full article →