Help on Formatting string

Database

Help on Formatting string

I am new to the Teradata Database, I am finding hard how to manipulate string functions. I have Data Field with Full Names as Ex: Janice Dawson Streat, Rob Freeman, John E Miller ..., but as per our business Requirement we just need the First letter of the First name and Last name as J Streat, R Freeman, J Miller... I am trying to use Substring(), Index() functions to ge the result but unable to figure out how exactly can I get the results. please help me on the syntax.
4 REPLIES
Enthusiast

Re: Help on Formatting string

Hi,
Try using the below sql. Just replace your SUSHIL.TEST1 w/ your database.tablename and name variable with your full name variable on the table.

select x.name
, x.mod_name
from (
SELECT trim(name) as name
, substr(name,1,1) as x
, substr ( name , index( name,' ') +1 ) as y
, substr ( y , index(y,' ') ) as z
, case
when y=z then x||' '||z
else x||z end
as mod_name

FROM SUSHIL.TEST1
) as x;

The sql worked for me, hope this works for you as well..

Thanks!
Enthusiast

Re: Help on Formatting string

Hi Sushil,

Would it be better to use trim(both ' ' from name)? I am not sure about Teradata to Teradata ETL but I have seen data from Oracle with padding on the right. If that is the case then your code would fail, at least in Teradata 12.
Enthusiast

Re: Help on Formatting string

Hi,
Please read about SUBSTR function esp about wht happens when we skip the 3rd parameter for CHAR datatype in the Teradata SQL ref documentation for Function. Below is a BTEQ script execution log which i ran today for you. Hope this helps.

+---------+---------+---------+---------+---------+---------+---------

create table tduser.test1
, no fallback
, no after journal
, no before journal
( name char(30))
primary index(name);

*** Table has been created.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------

insert into tduser.test1 values('Janice Dawson Streat');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------
insert into tduser.test1 values('Rob Freeman ');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------
insert into tduser.test1 values('John E Miller ');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------
insert into tduser.test1 values('SUSHIL IS TESTING ');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------
insert into tduser.test1 values('Testing SQL ');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------

select x.name
, x.mod_name
from (
SELECT trim(name) as name
, substr(name,1,1) as x
, substr ( name , index( name,' ') +1 ) as y
, substr ( y , index(y,' ') ) as z
, case
when y=z then x||' '||z
else x||z end
as mod_name

FROM tduser.test1
) as x;

*** Query completed. 5 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

name |mod_name
------------------------------ --------------------------------
Testing SQL |T SQL
Janice Dawson Streat |J Streat
SUSHIL IS TESTING |S TESTING
Rob Freeman |R Freeman
John E Miller |J Miller

+---------+---------+---------+---------+---------+---------+---------

.exit
*** You are now logged off from the DBC.
*** Exiting BTEQ...
*** RC (return code) = 0
Enthusiast

Re: Help on Formatting string

Hi Sushil,
Thanks for the explanation. I used varchar when I tried it but I see the manual says it trims trailing padding for bytes and chars. So that happened when you substr/index name as y. It got rid of the trailing space which works when you then substr/index y as z.