Change all periods "." in a column to forward slash "/"

Database
Enthusiast

Change all periods "." in a column to forward slash "/"

I am running a query that returns blling data with an account number that contains "." in no set position. What I need to do is replace the period "." in each instance that it occurs with a forward slash "/".

I have read many items related to using UDF's, however I do not own the server and have no idea if any UDFs are actually loaded.

The Release is 12.00.02.44

Version 12.00.02.40

Example of current results:








BILLING_NO BILL_CONTH_DT BILL_CYCLE_DT SUM(CHRGE_ACT) ACCOUNT_NO
123456789 4/1/2012 4/10/2012 240 108.T4.ABCDEFGHINM.KJIHABCDERG
         
Desired Reults:
BILLING_NO BILL_CONTH_DT BILL_CYCLE_DT SUM(CHRGE_ACT) ACCOUNT_NO
123456789 4/1/2012 4/10/2012 240 108/T4/ABCDEFGHINM/KJIHABCDERG

I am not a regular user of Teradata so I would appreciate a detailed explination - I am willing to learn :) !

Thanks for your time.

Tags (3)
18 REPLIES
Senior Apprentice

Re: Change all periods "." in a column to forward slash "/"

You should check if any of the Oracle functions oTranslate or oReplace exist in your system:

select databasename, tablename

from dbc.tablesV

where tablename in ('oReplace', 'oTranslate')

and TableKind = 'F'

If they existHopefully they exist (and you got the neccessary access rights) it's easy:

oReplace(account_no, '.', '/') or oTranslate(account_no, '.', '/')

Dieter

Enthusiast

Re: Change all periods "." in a column to forward slash "/"

I just checked - nothing there.

Enthusiast

Re: Change all periods "." in a column to forward slash "/"

Anyone have any suggestions or thoughts on how to accomplish this?

I appreciate your time and thanks for looking.

Gmoney

Senior Apprentice

Re: Change all periods "." in a column to forward slash "/"

Why do you need the change?

Is it one-time-only or do you need it for every query?

Dieter

Junior Supporter

Re: Change all periods "." in a column to forward slash "/"

Sorry for jumping in.

If you cannot use oReplace maybe some approach like this may help you:

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT ACC ORIGINAL,

       INDEX( ACC,'.') SEP1,

       SEP1+INDEX( SUBSTR(ACC, SEP1 + 1),'.') SEP2,

       SEP2+INDEX( SUBSTR(ACC, SEP2 + 1),'.') SEP3,

       SUBSTR(ACC,1,SEP1-1) PART1,

       SUBSTR(ACC,SEP1+1,SEP2 -(SEP1+1)) PART2,

       SUBSTR(ACC,SEP2+1,SEP3 -(SEP2+1)) PART3,

       SUBSTR(ACC,SEP3+1) PART4,

       PART1 || '/' || PART2 || '/' || PART3 || '/' || PART4 CHANGED

  FROM ( SELECT '108.T4.ABCDEFGHINM.KJIHABCDERG' ACC ) a

;

 *** Query completed. One row found. 9 columns returned.

 *** Total elapsed time was 1 second.

ORIGINAL                       SEP1 SEP2 SEP3 PART1 PART2 PART3       PART4       CHANGED

------------------------------ ---- ---- ---- ----- ----- ----------- ----------- ------------------------------

108.T4.ABCDEFGHINM.KJIHABCDERG    4    7   19 108   T4    ABCDEFGHINM KJIHABCDERG 108/T4/ABCDEFGHINM/KJIHABCDERG

The performance it's not been taken into account. Only 'self-explained', 'academic' example.

HTH.

Cheers.

Carlos.

Enthusiast

Re: Change all periods "." in a column to forward slash "/"

Dieter,

I need the change everytime the query runs. Primarily because this will end up being part of a cross-query against an Oracle query that will depend on the account_no being formated with the slashes.

For all intensive purposes the account_no in the Teradata db is a PK to the FK account_no in the Oracle db.

Carols,

I believe I see your reasoning but I do not believe it will work for this situation because I have various account_no formats to deal with, and the strng lengths are different.

For example another version could look like: 00.ABCD.123456.YZ .

Thanks for the follow up to both of you.

Senior Apprentice

Re: Change all periods "." in a column to forward slash "/"

Now there's another question:

Why the difference between TD and Oracle when it's supposed te be the same kind of information?

You could also use TRANSLATE on Oracle to change '/' to '.' :-)

Carlos suggestion will work if there's always a four-part account_no and could be adjusted to more or less parts. On TD13.10 you could hide the complex calculation in a SQL UDF, but it's still unneccessary overhead. You were talking about a few hundred rows, so this could be acceptable.

Dieter

Junior Supporter

Re: Change all periods "." in a column to forward slash "/"

It does not matter what the length is, it will work as long as there are three separators/four parts (I followed the format you provided):

SELECT ACC ORIGINAL,

       INDEX( ACC,'.') SEP1,

       SEP1+INDEX( SUBSTR(ACC, SEP1 + 1),'.') SEP2,

       SEP2+INDEX( SUBSTR(ACC, SEP2 + 1),'.') SEP3,

       SUBSTR(ACC,1,SEP1-1) PART1,

       SUBSTR(ACC,SEP1+1,SEP2 -(SEP1+1)) PART2,

       SUBSTR(ACC,SEP2+1,SEP3 -(SEP2+1)) PART3,

       SUBSTR(ACC,SEP3+1) PART4,

       PART1 || '/' || PART2 || '/' || PART3 || '/' || PART4 CHANGED

  FROM ( SELECT '00.ABCD.123456.YZ' ACC ) a

;

 *** Query completed. One row found. 9 columns returned.

 *** Total elapsed time was 1 second.

ORIGINAL          SEP1 SEP2 SEP3 PART1 PART2 PART3  PART4 CHANGED

----------------- ---- ---- ---- ----- ----- ------ ----- -----------------

00.ABCD.123456.YZ    3    8   15 00    ABCD  123456 YZ    00/ABCD/123456/YZ

If there are other different formats (variable number of separators/parts) I would try a recursive query.

Cheers.

Carlos.

Enthusiast

Re: Change all periods "." in a column to forward slash "/"

Carlos

Thanks for your follow up! I appreciate your patience with the newbie. What it boils down to then is that I did not know how to apply this to my existing query. After playing with it a little, of course, it does work as you and Dieter both said.

I also manged to "adjust it" to cover the variances in formats of ACCOUNT_NO.

Here is the query as I am running:

SELECT BILL_DTL.CABS_BILLING_NO,

       BILL_DTL.BILL_MONTH_DT,

       BILL_DTL.BILL_CYCLE_DT,

       sum(BILL_DTL.CHRGE_AMT (FORMAT '$999,999.99')), --> format not working no $    BILL_DTL.ACCOUNT_NO,

        INDEX( ACCOUNT_NO,'.') SEP1,

       SEP1+INDEX( SUBSTR(ACCOUNT_NO, SEP1 + 1),'.') SEP2,

       SEP2+INDEX( SUBSTR(ACCOUNT_NO, SEP2 + 1),'.') SEP3,

       SEP3+INDEX( SUBSTR(ACCOUNT_NO, SEP3 + 1),'.') SEP4,

       SUBSTR(ACCOUNT_NO,1,SEP1-1) PART1,

       SUBSTR(ACCOUNT_NO,SEP1+1,SEP2 -(SEP1+1)) PART2,

       SUBSTR(ACCOUNT_NO,SEP2+1,SEP3 -(SEP2+1)) PART3,

       SUBSTR(ACCOUNT_NO,SEP3+1,SEP4 -(SEP3+1)) PART4,

       SUBSTR(ACCOUNT_NO,SEP4+1) PART5,

       PART1 || '/' || PART2 || '/' || PART3 || '/' || PART4 || '/' || PART5 CHANGED

FROM edw_vwmc.BILL_DTL BILL_DTL

WHERE   (BILL_DTL.CABS_BILLING_NO = '60020555s3')

       AND (BILL_DTL.BILL_MONTH_DT =

               calendar__gregorian__current_month__)

       AND (BILL_DTL.ACCOUNT_NO LIKE '%442529%')

GROUP BY BILL_DTL.BILL_MONTH_DT,

         BILL_DTL.CABS_BILLING_NO,

         BILL_DTL.BILL_CYCLE_DT,

         BILL_DTL.ACCOUNT_NO

Now for the dumb question - I only want to display my original columns such as :

BILLING_NO BILL_CONTH_DT BILL_CYCLE_DT SUM(CHRGE_ACT) ACCOUNT_NO

How do I prevent the SEP1, PART 1 from displaying in the result set?

while keeping CHANGED  column in the display?

Regards,

GMoney