Strip titles from varchar string

[fayez]

Hi.

I have a large Address table, NAME col. is varchar and has titles like:
'MR. ABC and MRS. MMM' want to update to: 'ABC and MMM'
'MR. ABC and MRS. MMM' want to update to be: 'ABC and MMM'
'DR. KKK MMM' want to update to be: 'KKK MMM'

titles are long list (english, french..)

any ideas of a sql script or a function?
I am using DB2 udb 8.2 on aix. code is sql Stored Procedures

Thanks
Fayez

[Rhino]

"fayez" <fs****@gmail.c om> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

Hi.

I have a large Address table, NAME col. is varchar and has titles like:
'MR. ABC and MRS. MMM' want to update to: 'ABC and MMM'
'MR. ABC and MRS. MMM' want to update to be: 'ABC and MMM'
'DR. KKK MMM' want to update to be: 'KKK MMM'

titles are long list (english, french..)

any ideas of a sql script or a function?
I am using DB2 udb 8.2 on aix. code is sql Stored Procedures

Stored procedures and user-defined functions are intended for tasks that
you will do again and again. Therefore, if you want to *permanently* remove
the titles from the database, I think it would make more sense to create a
batch job to do a one-shot mass of your tables. A stored procedure or
used-defined function would make more sense if you simply want to *ignore*
the titles for some queries and return just the rest of the name.

Is there any possibility of restructuring your tables? Many queries on your
tables would be easier if you had separate columns for first name, last
name, middle names, title, "generation al identifier" (e.g. the "Jr." in
"John Doe Jr." or the "III" in "John Doe III"). Then, you could pick or
choose whichever aspects of the name you wanted in each case. You would also
find it easier to determine which part of the name was which. For instance,
if you saw the name "Forrest Sawyer" or "Jing Lee", you wouldn't have to
guess which was the first name and which was the last.

If you must stay with the single column to contain all the name information,
you would have to write logic like this (pseudo-code):

---

select name from customer_table;

if (first-word-of-name = 'Mr.') then short_name = rest-of-name
else if (first-word-of-name = 'Mrs.') then short_name = rest-of-name
etc. etc.

display rest-of-name in report or query result
---

But be careful; this will be vulnerable to occasional logical errors that
can't easily be handled in programs. For example, there is an actress named
Missy Crider. If she were in your table, your program would probably simply
strip off the first part of her first name and leave it as "y Crider". That
is obviously wrong and might upset her if she were a customer.

Rhino

[4.spam]

Hello.

------
declare global temporary table titles (t varchar(10) not null) with
replace on commit preserve rows;
declare global temporary table strings (s varchar(50) not null) with
replace on commit preserve rows;
declare global temporary table repl(orig varchar(50), new varchar(50))
with replace on commit preserve rows;

insert into session.titles values ('MR.'), ('MRS.'), ('DR.');
insert into session.strings values
('MR.AAA AND DR.BBB'),
('MR.AAA AND MRS.BBB'),
('MRS.DDD AND DR.FFF'),
('DR.CCC AND GGG'),
('EEE');

-- temporary table for replacement
insert into session.repl (orig, new)
WITH A(ORIG, ITER, S) AS (
SELECT S.S, 1, VARCHAR(REPLACE (S.S, T.T, ''), 50)
FROM SESSION.TITLES T
JOIN SESSION.STRINGS S ON REPLACE(S.S, T.T, '')!=S.S
UNION ALL
SELECT A.ORIG, A.ITER+1, VARCHAR(REPLACE (A.S, T.T, ''), 50)
FROM A, SESSION.TITLES T
WHERE REPLACE(A.S, T.T, '')!=A.S
)
SELECT DISTINCT ORIG, S
FROM A
WHERE ITER = (SELECT MAX(ITER) FROM A A2 WHERE A2.ORIG=A.ORIG) ;

update session.strings s
set s.s = (select r.new from session.repl r where r.orig=s.s)
where exists (select r.new from session.repl r where r.orig=s.s);
------

Sincerely,
Mark B.

[Tonkuma]

How about this?
BEGIN ATOMIC
FOR st AS
SELECT title
FROM titles_table
DO
UPDATE Address_table
SET name = REPLACE(name, title, '');
END FOR;
END!

[fayez]

Thanks for the idea, woking fine