Re: Gray Hair is still relevant
Date: 17 Jul 2006 07:52:32 -0700
Message-ID: <1153147952.877120.99030_at_p79g2000cwp.googlegroups.com>
I don't know what DBMS are you using.
This is an example of DB2. But, Oracle have also same
functionarity(Recursive query) using CONNECT BY.
(I'm sorry if I misunderstand your problem.)
CREATE FUNCTION Remove_Dup_N (I_Text VARCHAR(100), N INTEGER)
RETURNS VARCHAR(100)
LANGUAGE SQL
RETURN
WITH Find_Nth_Word (seq, sBar, eBar) AS (
VALUES (1, 0, POSSTR(I_Text||'|', '|') )
UNION ALL
SELECT seq + 1
, eBar
, LOCATE('|', I_Text||'|', eBar + 1)
FROM Find_Nth_Word
WHERE seq < 100
AND seq <= N - 1
)
SELECT SUBSTR(I_Text,1,eBar) ||
REPLACE(SUBSTR(I_Text||'|',eBar+1),
SUBSTR(I_Text||'|',sBar+1,eBar-sBar), '')
FROM Find_Nth_Word
WHERE seq = (SELECT MAX(seq) FROM Find_Nth_Word)
;
- Commands Entered -------------------------
WITH Recurse (N, Name, Categories, Cat_Count) AS (
SELECT 1
, Name
, Categories
, LENGTH(REPLACE(TRANSLATE(UPPER(Categories), '',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ', ''))+1 FROM Users UNION ALL SELECT N + 1
, Name
, Remove_Dup_N (Categories, N)
, LENGTH(REPLACE(TRANSLATE(UPPER(Categories), '',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ', ''))+1 FROM Recurse WHERE N < 100 AND N <= Cat_Count ) SELECT Name
, CAST(REPLACE(Categories, '||', '') AS VARCHAR(100)) AS
Categories FROM Recurse A WHERE N = (SELECT MAX(N) FROM Recurse B WHERE B.Name = A.Name ) ;
NAME CATEGORIES
Pak Sales|Engenner|Project Manager
Joe Engenner|Tester|Sales|Project Manager
Ann Secretary|Technician|Engenner|Technical Manager|Sales Manager
3 record(s) selected. Received on Mon Jul 17 2006 - 16:52:32 CEST