How to shorten, trim, lowercase, remove non-alphanumeric characters and update all at once
Posted on 2010-08-26
I'm a newbie at SQL and PL/SQL, so please bear with me...
I have 2 tables, both with multiple columns. In table A there is a column named NAME and in table B there is a column named SHORTNAME. Both of those columns are VARCHAR2(254 BYTE) and both tables have an ID column so I can join them up.
What I want to do is, where a.ID = b.ID, I want to take the value of a.NAME, remove any spaces (before, after and in between), change any chars to lower case, remove any characters that aren't a to z or 1 to 0, and "trim" the length to be a maximum of 10 alpha numeric characters. And finally, take the result and update it into b.SHORTNAME. Yes, a.NAME can be multiple words and it can include numbers and possibly other characters. I only want letters and/or numbers.
Here is an example result...
a.NAME = Experts Exchange
There are about 2k rows that I need to update, so I'm assuming that this would be done in PL/SQL? I don't know, I've never written anything this complicated in PL/SQL.
I hope I was clear enough. If not, please let me know.