Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

search values inside table

Posted on 2011-02-18
6
Medium Priority
?
409 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:akp007
  • 4
6 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34930612
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 34930657
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
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34931795
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34934479
>>(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 Closing Comment

by:akp007
ID: 34976785
Thanks slightwv. Your assumptions were correct. sorry for not explaining the problem clearly

Regards
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34979103
Glad I guessed right.  Glad to help.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question