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

akp007Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor 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
What about the name Miller?
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;

Open in new window

0
 
slightwv (䄆 Netminder) 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
 
Aaron ShiloChief 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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
slightwv (䄆 Netminder) 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
 
akp007Author Commented:
Thanks slightwv. Your assumptions were correct. sorry for not explaining the problem clearly

Regards
0
 
slightwv (䄆 Netminder) Commented:
Glad I guessed right.  Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.