Clean Part Number (CleanMPN)

The purpose of this page is to discuss alternatives for maintaining a clean (free of any special characters - examples: "!@#$%^&*()" ) part number or MPN. If you ever need to mine data where the text is disturbed by special characters, this discussion will help.

Function to Clean a String

CREATE OR REPLACE FUNCTION chuboe_cleanmpn (text) RETURNS text AS $$
SELECT regexp_replace($1, '[^\w]+|_','','g') 
$$ LANGUAGE sql;

Discussion points:

'[^\w]+' removes most special characters
'[^\w]+|_' adds underscore to the list of removed characters
'g' makes the replace global to the test string
You can execute this function as part of an update statement: update sometable set chuboe_mpn_clean = chuboe_cleanmpn(chuboe_mpn);

Plugin

You can create a simple model event plugin. That sets a chuboe_mpn_clean field as part of a beforeSave event.