By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

Strip titles from varchar string

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"fayez" <fs****@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.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, "generational 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
Nov 12 '05 #2

P: n/a
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.

Nov 12 '05 #3

P: n/a
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!

Nov 12 '05 #4

P: n/a
Thanks for the idea, woking fine

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.