Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

search values inside table

Posted on 2011-02-18
6
Medium Priority
?
408 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 77

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 77

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 77

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 77

Expert Comment

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

730 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