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
Medium Priority
408 Views
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
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
• 4

LVL 77

Expert Comment

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

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
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

LVL 15

Expert Comment

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

LVL 77

Expert Comment

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

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

Regards
0

LVL 77

Expert Comment

ID: 34979103
0

## Featured Post

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…
###### Suggested Courses
Course of the Month11 days, 13 hours left to enroll