Home » SQL & PL/SQL » SQL & PL/SQL » Search in concatenated string (merged)
Search in concatenated string (merged) [message #664158] Wed, 05 July 2017 13:01 Go to next message
patneel
Messages: 47
Registered: June 2007
Location: India
Member
Hi,

I have single list of employee ID's and concatenated list of employee ID's stored in two separate tables.
concatenated list not correct at some places as observations but I want to list out such all records where concatenated list different than single list.

Example here concatenated list is incomplete compared to single list. I am not sure to use oracle functions like regexp_like or INSTR in such case. please let me know if any suggestions

Table 1

Dept_ID	Employee_ID
101	415P
101	I999
101	J766
101	K565
101	Y234
	
Table 2
	
Dept_ID	Concat_Employee_ID
101	415P;I999;J766
Search in concatenated string [message #664159 is a reply to message #664158] Wed, 05 July 2017 13:04 Go to previous messageGo to next message
patneel
Messages: 47
Registered: June 2007
Location: India
Member
Hi,

I have single list of employee details and concatenated employee details stored in two different tables.
As per observations, single list of employee details not matching correctly with concatenated list and i need to list down such all records against mismatch

Dept ID	Employee ID
101	415P
101	I999
101	J766
101	K565
101	Y234
	
	
Dept ID	Concat Employee ID
101	415P;I999;J766

Here concatenated employee list is incomplete when compared with singe list. I am not sure how to use oracle functions like regexp_like or INSTR for such cases. Please let me know if any suggestions.
Re: Search in concatenated string (merged) [message #664163 is a reply to message #664158] Wed, 05 July 2017 15:15 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 21 May 2015 19:47

Don't forget:
Michel Cadot wrote on Mon, 23 July 2012 07:50
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Here's a example for 11gR2:
SQL> select deptno, listagg(ename,',') within group (order by ename) names
  2  from emp
  3  group by deptno
  4  order by deptno
  5  /
    DEPTNO NAMES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

And don't forget to feedback to your topics.


Previous Topic: Recursive select
Next Topic: Accessing Sub directory under Oracle directory
Goto Forum:
  


Current Time: Fri Mar 29 03:54:46 CDT 2024