Home » SQL & PL/SQL » SQL & PL/SQL » Retrieving list of elements from a Large Memo field
Retrieving list of elements from a Large Memo field [message #218336] Wed, 07 February 2007 15:45 Go to next message
barnard90
Messages: 4
Registered: December 2006
Junior Member
I have an Employee table which has a field called
" Employee_list "

The structure is
Deptno Deptname Employee_List

This field has all the employee ids working a specific department stored in it separated by commas .
The data in Employee_list field is like 101 , 102, 103, 104 ...etc


The data in the current table is

Deptno Deptname Employee_List
100 Accounting 101,102,103,104
200 Sales 201,202,203,204

I would like to separate the employees from the Employee_List field and report them as individual employee ids in a separate view


The structure of the new view is

Deptno Deptname Emp_id
100 Accounting 101
100 Accounting 102
100 Accounting 103

How do I do that ? Please suggest
Re: Retrieving list of elements from a Large Memo field [message #218402 is a reply to message #218336] Thu, 08 February 2007 01:21 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The forum has several threads on how to split a string.

Here's a thread addressing a similar issue.

Abusing ebrian's method you could try this if you're on 10g:
WITH yourtable AS
     (SELECT 100 dno
           , 'Accounting' dname
           , '101,102,103,104' emplist
      FROM   DUAL
      UNION ALL
      SELECT 200 dno
           , 'Sales' dname
           , '201,202,203,204' emplist
      FROM   DUAL)
SELECT DISTINCT
           REGEXP_SUBSTR (emplist, '[^,]+', 1, LEVEL) empno
     ,     dname
     ,     dno
FROM       (SELECT emplist
                 , dname
                 , dno
            FROM   yourtable)
CONNECT BY REGEXP_SUBSTR (emplist, '[^,]+', 1, LEVEL) IS NOT NULL
order by dno, empno


Thanks ebrian, regular expressions can be fun! Very Happy

MHE

[Updated on: Thu, 08 February 2007 01:21]

Report message to a moderator

Previous Topic: connection problem
Next Topic: ODBC Driver - PL/SQL Write to Paradox Tables
Goto Forum:
  


Current Time: Thu Dec 08 21:53:17 CST 2016

Total time taken to generate the page: 0.06550 seconds