Home » SQL & PL/SQL » SQL & PL/SQL » Searching a string and replacing with value (Oracle 10g)
Searching a string and replacing with value [message #612069] Fri, 11 April 2014 11:27 Go to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Hi All,

alter table dept add(mask_field varchar2(100),mask_value varchar2(100));

update dept set mask_field='ename,job,',mask_value=q'[ename||x,job||y,]' where deptno=10;
update dept set mask_field='sal',mask_value=q'[xxxx]' where deptno=20;

select empno,decode('sal',mask_field,mask_value,sal) sal,
ename,job,dept.deptno from emp,dept
where emp.deptno=dept.deptno;


I want to put the decode on ename column in the above query, like decode('ename',mask_field,mask_value,ename)
and it should try to find the ename string in the mask_field if found then it should take the first value .The same needs to happen in case of job field also.job is appearing in the second field hence it should take the second value based on the comma separator. It is working fine for single value but for multiple value the problem is occuring. Please help.

Regards,
Nathan

[MERGED by LF]

[Updated on: Thu, 17 April 2014 12:45] by Moderator

Report message to a moderator

Re: Searching a string and replacing with value [message #612070 is a reply to message #612069] Fri, 11 April 2014 11:33 Go to previous messageGo to next message
manubatham20
Messages: 443
Registered: September 2010
Location: Champaign, IL
Senior Member

Use INSTR along with decode.

Something like decode(instr(mask_field,ename),1,a,b)

Manu
Re: Searching a string and replacing with value [message #612071 is a reply to message #612070] Fri, 11 April 2014 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 22485
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Searching a string and replacing with value [message #612072 is a reply to message #612070] Fri, 11 April 2014 11:41 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member


Thanks for reply.It is not like only if found then take the value, if found on before first comma, it should take the value which is present before the first comma.
Re: Searching a string and replacing with value [message #612074 is a reply to message #612072] Fri, 11 April 2014 11:48 Go to previous messageGo to next message
manubatham20
Messages: 443
Registered: September 2010
Location: Champaign, IL
Senior Member

Try to play with below code. And post a example before you ask any question again.

with temp as
(select 'xxxMANUxxx,BATHAM' mask_field, 'MANU' ename from dual)
select decode(instr(substr(mask_field,1,instr(mask_field,',')),ename),0,'NOT FOUND','FOUND') from temp;


Manu
Function to get column value [message #612272 is a reply to message #612069] Tue, 15 April 2014 08:41 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Hi All,

create or replace function test_p(p_str in varchar2) return varchar2 is
  l_str varchar2(100);
begin
  select ename into l_str from test_emp;
   return l_str || 'EXTRA';
  
  --return p_str||'EXTRA';

end;

create table test_emp(ename varchar2(20)); insert into test_emp(ename) values('ename'); commit;

SELECT test_p(ENAME) FROM EMP;



If I am only using the function it is returning the data with the ename like MILLEREXTRA.
How can I get the same value by using the above function. Please help.

Regards,
Nathan
Re: Function to get column value [message #612274 is a reply to message #612272] Tue, 15 April 2014 08:46 Go to previous messageGo to next message
pointers
Messages: 345
Registered: May 2008
Senior Member
I am not sure about the background of this why you are creating funcion to do this -
however, assign the function IN value (p_str) to l_str and then return, no need to call test_emp from the function as you shown.

Regards,
Pointers
Re: Function to get column value [message #612277 is a reply to message #612272] Tue, 15 April 2014 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58514
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand what you want to do with the function.
Note you have only one row in test_emp containing the value "ename". Where does MILLER come from?
Also you do not use the parameter in your function, so why there is a parameter?
And many other things.
Not clear.
Explain in details what is the issue and question.

Re: Function to get column value [message #612278 is a reply to message #612272] Tue, 15 April 2014 09:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1797
Registered: May 2013
Location: World Wide on the Web
Senior Member
Never use PLSQL when it could be done in plain SQL.

You just need to write a query to select the column value and concatenate with the string you want. If your requirement is something else, please elaborate.
Re: Searching a string and replacing with value [message #612345 is a reply to message #612074] Wed, 16 April 2014 06:17 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member



alter table dept add(mask_field varchar2(100),mask_value varchar2(100));

update DEPT set MASK_FIELD='ename,job,',MASK_VALUE=q'[ename||x,job||y,]' where DEPTNO=10;
update DEPT set MASK_FIELD='sal,',MASK_VALUE=q'[sal||xxxx,]' where DEPTNO=20;

COMMIT;
CREATE OR replace FUNCTION Test_pp(p_str VARCHAR2, p_col VARCHAR2, p_val VARCHAR2) 
RETURN VARCHAR2 
IS 
  l_str    VARCHAR2(100) := p_str; 
  l_val    VARCHAR2(100) := p_val; 
  l_col    VARCHAR2(100) := p_col; 
  l_found  VARCHAR2(100); 
  l_fndval VARCHAR2(100); 
  l_cnt    NUMBER := 0; 
  l_st     NUMBER := 0; 
BEGIN 
    WHILE( Instr(l_str, ',') > 0 ) LOOP 
        l_cnt := l_cnt + 1; 
        l_found := Substr(l_str, 1, Instr(l_str, ',') - 1); 
        l_fndval := Substr(l_val, 1, Instr(l_val, ',') - 1); 
        IF l_found = l_col THEN 
          l_st := 1; 
          exit; 
        END IF; 
        l_str := Substr(l_str, Instr(l_str, ',') + 1); 
        l_val := Substr(l_val, Instr(l_val, ',') + 1); 
    END LOOP; 

    IF l_st = 0 THEN 
      RETURN NULL; 
    ELSE       
      RETURN l_fndval; 
    end if; 
end;  

select NVL(TEST_PP(MASK_FIELD,'sal',MASK_VALUE),SAL) SAL ,
NVL(TEST_PP(MASK_FIELD,'ename',MASK_VALUE),ENAME)ENAME, 
NVL(test_pp(mask_field,'job',mask_value),job)job
from emp,dept
where emp.deptno=dept.deptno;




I AM GETTING the EXCEPTED value BUT HOW the ENAME ,SAL,job WILL return the EXACT value of the EMP
table but not the column name itself. Please help.

REGARDS,
Nathan
Re: Searching a string and replacing with value [message #612386 is a reply to message #612345] Thu, 17 April 2014 07:34 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

alter table emp add ssn varchar2(15);

update emp set ssn=	'101-64-9265'	 where empno=	7369	;
update emp set ssn=	'106-41-6883'	 where empno=	7499	;
update emp set ssn=	'115-93-9248'	 where empno=	7521	;
update emp set ssn=	'130-56-7205'	 where empno=	7566	;
update emp set ssn=	'162-28-7445'	 where empno=	7654	;
update emp set ssn=	'163-79-5574'	 where empno=	7698	;
update emp set ssn=	'165-01-9076'	 where empno=	7782	;
update emp set ssn=	'175-58-2875'	 where empno=	7788	;
update emp set ssn=	'187-16-8016'	 where empno=	7839	;
update emp set ssn=	'192-10-5480'	 where empno=	7844	;
update emp set ssn=	'302-07-7274'	 where empno=	7876	;
update emp set ssn=	'327-85-5856'	 where empno=	7900	;
update EMP set SSN=	'826-73-4221'	 where EMPNO=	7902	;
update emp set ssn=	'626-73-4298'	 where empno=	7934	;

update dept set mask_field=	'ssn~sal~'	,mask_value	='REGEXP_REPLACE(ssn,''(\-[[:digit:]]{4})'',''-XXXX'')~REGEXP_REPLACE(sal,''([[:digit:]](*))'',''X'')~'	 where deptno=10;
update dept set mask_field=	'ssn~ename~'	,mask_value='REGEXP_REPLACE(ssn,''(\-[[:digit:]]{2}\-)'',''-XX-'')~REGEXP_REPLACE(ename,''([[:alnum:]](*))'',''X'') ~'	 where deptno=20;
update dept set mask_field=	'ssn~comm~'	,mask_value='REGEXP_REPLACE(ssn,''([[:digit:]]{3}\-)'',''XXX-'')~REGEXP_REPLACE(comm,''([[:digit:]](*))'',''X'')~'	 where deptno=30;

commit;

CREATE OR replace FUNCTION get_value(p_str IN VARCHAR2) 
RETURN VARCHAR2 
IS 
  L_STR      varchar2(100); 
  L_QUERY    varchar2(4000); 
  c1         SYS_REFCURSOR; 
BEGIN 
    IF p_str IS NULL THEN 
      RETURN NULL; 
    END IF; 

    L_QUERY := 'select ' || p_str || ' from dual '; 

    OPEN c1 FOR L_QUERY; 
    LOOP 
        FETCH c1 INTO l_str; 
        exit WHEN c1%NOTFOUND; 
    END LOOP; 

    return L_STR; 
END;  

--test_pp is same as the above procedure but replace , with ~


SELECT dept.deptno,empno,hiredate,job, 
       Nvl(Get_value(Replace(Test_pp(mask_field, 'ename', mask_value), 'ename', '''' ||ename ||'''') ), ename) ENAME, 
       Nvl(Get_value(Replace(Test_pp(mask_field, 'ssn', mask_value), 'ssn', '''' || ssn || '''')), ssn)SSN, 
       NVL(GET_VALUE(replace(TEST_PP(MASK_FIELD, 'sal', MASK_VALUE), 'sal', '''' || SAL || '''')), SAL) SAL, 
       Nvl(Get_value(Replace(Test_pp(mask_field, 'comm', mask_value), 'comm', '''' ||comm ||'''')), comm) comm 
from   EMP, DEPT 
WHERE  emp.deptno = dept.deptno;  




the all DEPTNOS ARE having DIFFERENT columns with CUSTOMIZED data in DEPT.the INTENTION is to FETCH the data ACCORDING to the DEPT.
Please suggest anything wrong in the above process.

[Updated on: Thu, 17 April 2014 07:36]

Report message to a moderator

Re: Function to get column value [message #612391 is a reply to message #612278] Thu, 17 April 2014 07:55 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

This is part of the thread Searching a string and replacing with value
Re: Function to get column value [message #612400 is a reply to message #612391] Thu, 17 April 2014 12:45 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why did you, then, create a new topic? To make confusion? If so, you succeeded.
Re: Function to get column value [message #612465 is a reply to message #612400] Sat, 19 April 2014 01:55 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Sorry, I started a new thread to make previous question a little bit simpler ,so that I can find the answer.But both the times I did not succeed.

[Updated on: Sat, 19 April 2014 01:55]

Report message to a moderator

Re: Function to get column value [message #619159 is a reply to message #612465] Fri, 18 July 2014 08:44 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Dear LittleFoot,

I would like to apologize of my misbehaviour towards you in a thread. It was probably a mistake for not providing exact requirement so that anyone will face the problem to understand it.
In future I promise that I will respect to all the members of this forum.You all have given so much supports through out from start till this time, I must be thankful to you.Without you all this journey can never
be successful.Please forgive me this time and give some comment to my posts which you had stopped so long back.

Regards,
Nathan
Re: Function to get column value [message #619173 is a reply to message #619159] Fri, 18 July 2014 10:42 Go to previous message
Lalit Kumar B
Messages: 1797
Registered: May 2013
Location: World Wide on the Web
Senior Member
In this thread itself, you are yet to feedback to few suggestions. Did you try plain SQL as I suggested?
Previous Topic: Reading the xml value
Next Topic: Table statistics
Goto Forum:
  


Current Time: Wed Jul 23 13:49:20 CDT 2014

Total time taken to generate the page: 0.12638 seconds