Archives of the TeradataForum

Message Posted: Wed, 28 Jan 2004 @ 20:22:48 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wednesday, January 28, 2004 15:12 -->

/* Note: tables a, c, and d were built just to demonstrate the extraction code. In reality, your data will probably be coming from a pre- existing table and thus your implementation of the above code should be alot less clunky than that displayed in the above example. -Scott Thompson
*/

/* Now I modified Scott's query to include two rows from our data. As we can see from the output included below, I will need to include an exception clause to pull the last county that doesn't have a trailing comma.

Some of our rows have up to 254 or 255 counties, in addition to our Primary Keys, so I still need to pivot the county data into a single county attribute.
*/

SEL X.PRIMKEY, X.INPSTRING, X.OUTSTRING1, X.OUTSTRING2, X.OUTSTRING3
FROM (
   select A.PRIMKEY AS PRIMKEY, A.INPSTRING AS INPSTRING
, (case when mindex(A.INPSTRING, ',') > 0
  then substring(A.INPSTRING from 1 for mindex(A.INPSTRING, ',') - 1)
 else NULL END)  AS OUTSTRING1

,  (case when mindex(A.INPSTRING, ',') > 0
  then substring(A.INPSTRING from mindex(A.INPSTRING, ',') + 1)
 else NULL END)  AS REMAINDER1
,   (case when mindex(REMAINDER1, ',') > 0
  then substring(REMAINDER1 from 1 FOR mindex(REMAINDER1, ',') -1)
 else NULL END)  AS OUTSTRING2
,  (case when mindex(REMAINDER1, ',') > 0
  then substring(REMAINDER1 from mindex(REMAINDER1, ',') + 1)
 else NULL END)  AS REMAINDER2
,  (case when mindex(REMAINDER2, ',') > 0
  then substring(REMAINDER2 from 1 FOR mindex(REMAINDER2, ',') -1)
 else NULL END)  AS OUTSTRING3
,  (case when mindex(REMAINDER2, ',') > 0
  then substring(REMAINDER2 from mindex(REMAINDER2, ',') + 1)
 else NULL END)  AS REMAINDER3

  FROM   (
SEL C.* FROM (SEL '19' AS PRIMKEY
, '1,9,29,73,77,115,165' AS INPSTRING
)  AS C

  UNION
SEL D.* FROM (
SEL '19' AS PRIMKEY
, '9' AS INPSTRING
)  AS D
)  AS A
(PRIMKEY, INPSTRING)

) AS X
ORDER BY 1 ;

/* the output
PRIMKEY     INPSTRING               OUTSTRING1  OUTSTRING2  OUTSTRING3
state   counties
19          9                       NULL        NULL        NULL
19          1,9,29,73,77,115,165    1           9           29

Removed all but 3 counties.
PRIMKEY     INPSTRING   OUTSTRING1   OUTSTRING2   OUTSTRING3
19          9           NULL         NULL         NULL
19          1,9,29      1            9            NULL

Removed all but two counties.
PRIMKEY     INPSTRING   OUTSTRING1   OUTSTRING2   OUTSTRING3
19          9           NULL         NULL         NULL
19          1,9         1            NULL         NULL

The code does not pull the last county. There is no trailing comma.
end the output */

	<Prev	Next>		<<First	<Prev	Next>	Last>>