asked on

Replace data from lookup table dramatically

Gurus,

I'm trying to create a dramatic way to replace data from a lookup table.

This works like a champ:

update PRO set PROVF5C = replace(PROVF5C,'cell','mobile')

However, I want to store all the values in a lookup table and have it be completely dramatic.

Example:

CREATE TABLE LOOKUP_TABLE
(tabkey NUMBER(12),
      lankeyi NUMBER(12),
      words VARCHAR2(250));

insert into lookup_table values (1,1,'cell');
insert into lookup_table values (1,13,'mobile');
insert into lookup_table values (2,1,'color');
insert into lookup_table values (2,13,'colour');

select * from lookup_table;
TABKEY LANKEYI WORDS
1        1        cell
1        13        mobile
2        1        color
2        13        colour

Before:
LOOKUP_TABLE.WORDS = 'mobile'
PRO.PROVF5C = 'My cell phone is dead'

What is the best option for replacing data in my PRO table with data from LOOKUP_TABLE?

After:
PRO.PROVF5C = 'My mobile phone is dead'

Any help would be appreciated

Sean Stuber

BEGIN
-- loop through old words
FOR old IN (SELECT *
FROM lookup_table
WHERE lankeyi = 1)
LOOP
-- For each old word, find the corresponding new word and update the table
UPDATE pro
SET provf5c =
REPLACE(provf5c,
old.words,
(SELECT words
FROM lookup_table
WHERE lankeyi = 13 AND tabkey = old.tabkey)
);
END LOOP;
END;

Kyle Abrahams, PMP

No loops necessary, just SQL 2005 or above.

NOTE: I changed the lookup table so w was the word to search for and V is the replacement word.

-- temp tables.
select 1 id, cast ('my color cell phone is dead' as varchar(100)) comment into #pro
insert into #pro
select 2, 'check out the color'

select 1 tabkey, cast ('cell' as varchar(20)) w , 'mobile' v
into #lkup

insert into #lkup select 2, 'color', 'colour'

-- BEFORE
select * from #pro
select * from #lkup

-- update
update MSTR
set
comment = new_comment
from
#pro MSTR
join
(

select

p.id,
(

            select
            STUFF((SELECT ' ' + newword
            FROM
            (
            select id, occurrence, isnull(REPLACE(a.display_term, a.display_term, lk.v), a.display_term) newWord
            from #pro m

            cross apply
             sys.dm_Fts_parser('"' + comment + '"' , 1033, 0,0) a
             left join #lkup lk on a.display_term = lk.w
            where m.id = p.id
            ) a

            FOR XML PATH('')), 1, 1, ''
            )

) new_comment

from #pro p

)old

on old.ID = MSTR.ID

-- AFTER
select * from #pro

-- CLEANUP
drop table #pro
drop table #lkup

Kyle Abrahams, PMP

One other note, I used sys.dm_Fts_parser as I didn't have a split function on the current DB I'm working on.

see here:
https://www.experts-exchange.com/questions/26425025/What-is-the-SQL-split-command.html

for a split function if you want to avoid using the system function.

xbox360dp

ASKER

Hi Stuber,

This is great!

However, I will need to put this in a trigger and the PRO table is the table being used.

Ideas?

xbox360dp

ASKER

Thanks for the input ged325 but this is in Oracle.

Sean Stuber

ged325 - I believe this is an oracle question based on the create table syntax.

Sean Stuber

assuming you are working with a before each row trigger, then the body of the trigger would look something like this...

BEGIN
-- loop through old words
FOR old IN (SELECT *
FROM lookup_table
WHERE lankeyi = 1)
LOOP
-- For each old word, find the corresponding new word and update the table
:new.provf5c := REPLACE(:new.provf5c,
old.words,
(SELECT words
FROM lookup_table
WHERE lankeyi = 13 AND tabkey = old.tabkey)
);
END LOOP;
END;

same as above, but instead of issuing an update on the table, you update the :new pseudo-record

awking00

What dbms are you using?

Sean Stuber

Oracle - http:#a38764415

xbox360dp

ASKER

Sdstuber,

Looks like there is a syntax issue ...

:new.provf5c := REPLACE(:new.provf5c,
old.words,
(SELECT words
FROM lookup_table
WHERE lankeyi = 13 AND tabkey = old.tabkey)
);

Is this possible to do with the replace function?

Sean Stuber

I'm using the replace function but, I messed up the usage when I took it out of a sql update and put in the trigger assignment.

Try this (this one I actually tested)

CREATE OR REPLACE TRIGGER trg_replace_words
BEFORE INSERT OR UPDATE
ON pro
FOR EACH ROW
BEGIN
-- Loop through old words
-- For each old word, find the corresponding new word and update the table
FOR replacment IN (SELECT o.words old_word,
(SELECT words
FROM lookup_table n
WHERE n.lankeyi = 13 AND n.tabkey = o.tabkey)
new_word
FROM lookup_table o
WHERE lankeyi = 1)
LOOP
:new.provf5c := REPLACE(:new.provf5c, replacment.old_word, replacment.new_word);
END LOOP;
END;

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xbox360dp

ASKER

Excellent, works like a champ!