# search values inside table

Dear experts -

I have created a table A with 2 columns.   I have an other 2nd  table where there is column which is varchar. The problem I am trying to solve is

I might have the data in the 2 nd table looks like  ' speed is 2 MI per hour'

table A has a mapping MI - Miles. I need to display/ convert as 'Speed is 2 Miles per hour'

Regards

###### Who is Participating?

Commented:
Until we get your final requirements, here's a quick test I cooked up.

It produces the results:
Speed is 2 Miles per hour
Speed is 2 Miles per hour 2 Feet per second
``````drop table tab1 purge;
create table tab1(key varchar2(10), value varchar2(10));

drop table tab2 purge;
create table tab2(col1 varchar2(50));

insert into tab1 values('MI','Miles');
insert into tab1 values('FT','Feet');

insert into tab2 values('Speed is 2 MI per hour');
insert into tab2 values('What about the name Miller?');
insert into tab2 values('Speed is 2 MI per hour 2 FT per second');
commit;

create or replace function myConvert(inStr in varchar2) return varchar2 is
retval varchar2(4000);
begin
retval := ' ' || inStr || ' ';
for i in (select key, value from tab1) loop
retval := replace(retval,' ' || i.key || ' ',' ' || i.value || ' ');
end loop;

return trim(retVal);
end;
/

show errors

select myConvert(col1) from tab2;
``````
0

Commented:
Not sure I understand all the combinations but a simple replace from tableA codes, values in table2 seems like it might work.

A small test case with tables, sample data and expected results would help a lot.

This will likely involve creating a function.  Is this an option?
0

Chief Database ArchitectCommented:
hi

there is no real solution for your problem that will hold for the long run. (i think all experts will agree)

you need to create another column with the relevant MI KM and any other value you wish to display or manipulate.
it would be perfect if you held a KEYWORD table and code it and reference it.

0

Commented:
>>(i think all experts will agree)

I don't agree.

I don't think there is enough information on the actual requirements to even begin to make that comment.  Based on the high level requirements listed, the code I posted does EXACTLY what they asked.
0

Author Commented:
Thanks slightwv. Your assumptions were correct. sorry for not explaining the problem clearly

Regards
0

Commented: