Archives of the TeradataForum

Message Posted: Mon, 30 Apr 2012 @ 12:44:50 GMT

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

Hi Anomy,

I have a kind-of crude approach to this -

1. Locate the position of the 1st '.' in your string and then substring ("position of '.'" - 6) till end. This will result in - "nt/DWI.CPC.IRA.RATNG.DAILY".

2. Now locate the 1st "/" and substring ("position of '/'" + 1) till end.

Query will look like this -

     sel substr(substr(
     '/scm/edw/appl/download/daily/current/DWI.CPC.IRA.RATNG.DAILY',
     (position('.' in
     '/scm/edw/appl/download/daily/current/DWI.CPC.IRA.RATNG.DAILY') -6), 100),(position('/'
     in substr( '/scm/edw/appl/download/daily/current/DWI.CPC.IRA.RATNG.DAILY',
     (position('.' in
     '/scm/edw/appl/download/daily/current/DWI.CPC.IRA.RATNG.DAILY') -6), 100)) + 1),100)

The reason why this is crude is, its subject to a few conditions -

1. DWI.CPC.IRA.RATNG.DAILY seems like a MF dataset, you need to confirm the max length of the 1st qualifier and accordingly change your 1st substring to ensure accurate result.

2. Also, the last qualifier of the path "/scm/edw/appl/download/daily/current/" will matter in the 1st substring.

So if you can check the data and fix the range of these two, you will be in a better position.

Thanks,

Debojit.

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