How to shorten, trim, lowercase, remove non-alphanumeric characters and update all at once
Hello,
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
b.SHORTNAME=expertsexc
OR:
a.NAME=Hey! You!
b.SHORTNAME=heyyou
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.
Thanks,
biglarrrr
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
b.SHORTNAME=expertsexc
OR:
a.NAME=Hey! You!
b.SHORTNAME=heyyou
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.
Thanks,
biglarrrr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops, thanks for the addition
ASKER
Thanks everyone. Below is the PL/SQL I came up with and it worked!
Thanks,
biglarrrr
Thanks,
biglarrrr
begin
for rec in ( select id,name from tablea where name IS NOT NULL )
loop
update tableb
set shortname = substr(regexp_replace(lower(rec.name),'[^a-z0-9]'),1,10)
where id = rec.id;
end loop;
end;