Search in concatenated string (merged) [message #664158] |
Wed, 05 July 2017 13:01 |
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 |
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 |
|
Michel Cadot
Messages: 68694 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:50With 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.
|
|
|