SQL for Parsing a String Delimited with Periods

[adventurous1]

Hi all...

In a TERADATA table, I have the following data stored in a field called DIRECTORY:

PDN.RAD.40391.0110.IA0
PDN.SW.69740.HDQ.IWB1
OMH.WDW.216942.Standard.119988
DCHH.HH.44463.OB.RR

I need to separate the characters between the periods into separate fields.

Can anyone help?

Thanks!

Ezekiel

 

[adventurous1]

One more thing...

I only need the first three entries...

Example:

PDN.RAD.40391.0110.IA0

Field 1 = PDN
Field 2 = RAD
Field 3 = 40391

Thanks,

Ezekiel

 

[bkj123]

if it were a flat file you could load it as a delimited file. Not sure if you can do this table to table or table to file to table. other than that, you basically need to find the position of the first 3 periods and then you can use a substring funtion to pull out the first 3 values.

I'd start with something like the code below. I'm 99.9% sure that the syntax, etc. is off, but I think you'll get the idea. best of luck - brian
SELECT
POSITION(COLUMNX,',' AS PERIOD1
,POSITION(SUBSTR(COLUMNX,PERIOD1+1),100,',') AS PERIOD2
,POSITION(SUBSTR(COLUMNX,PERIOD2+1),100,',') AS PERIOD3
,SUBSTR(COLUMNX,1,PERIOD1-1) AS FIELD1
,SUBSTR(COLUMNX,PERIOD1+1,PERIOD2-PERIOD1-1) AS FIELD2
,SUBSTR(COLUMNX,PERIOD2+1,PERIOD3-PERIOD2-PERIOD1-1) AS FIELD3

 

[al1024]

You can use "derived columns"... :

select 'ABC.DEF.GHI.JKL.MNO' as MYCOLUMN
, substring(MYCOLUMN from 1 for (position('.' in MYCOLUMN) (named POSDOT1)) - 1) as FIELD1
, substring((substring(MYCOLUMN from POSDOT1 + 1) (named SUB1)) from 1 for (position('.' in SUB1) (named POSDOT2)) - 1) as FIELD2
, substring((substring(SUB1 from POSDOT2 + 1) (named SUB2)) from 1 for (position('.' in SUB2) (named POSDOT3)) - 1) as FIELD3