Solved

search values inside table

Posted on 2011-02-18
6
404 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 76

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 76

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 76

Expert Comment

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

775 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