TD: select column_name, SUBSTRING(column_name FROM 11 FOR INDEX(column_name,'_')) from table_name;

Database
Enthusiast

TD: select column_name, SUBSTRING(column_name FROM 11 FOR INDEX(column_name,'_')) from table_name;

Hi, i have table with one column with data like :

C_Stage_8_NoLoseBet_Offer5 
C_Stage_3_Coupon_Offer7 
C_Stage_3_Coupon_Offer7 
C_Stage_4_ExistingPlayerSurvey_Offer4 
and i need to trim a substring like: 

NoLoseBet

Coupon

Coupon

ExistingPlayerSurvey

 

But the TD code is not work. Any help?

 

The sql source code is :

SELECT  
column_name,
SUBSTRING(column_name,11,CHARINDEX('_',column_name,11)-11)  FROM table_names ;

3 REPLIES
Junior Contributor

Re: TD: select column_name, SUBSTRING(column_name FROM 11 FOR INDEX(column_name,'_')) from table_na

CHAR_INDEX is proprietary SQL Server syntax, in Teradata there's INSTR:

Substring(Column_Name From 11 FOR Instr(Column_Name, '_', 11) -11)

But there's a much easier way to get the string between 3rd and 4th underscore:

StrTok(Column_Name, '_', 4)

 

Enthusiast

Re: TD: select column_name, SUBSTRING(column_name FROM 11 FOR INDEX(column_name,'_')) from table_na

Hi, tnx a lot
can you explain me what "4" is mean in 

StrTok(Column_Name, '_', 4)?
Senior Apprentice

Re: TD: select column_name, SUBSTRING(column_name FROM 11 FOR INDEX(column_name,'_')) from table_na

Hi,

 

The '4' is the 'tokennum' (https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1145_151K/STRTOK....).

 

STRTOK splits a string into parts (called 'tokens') and this parameter says which token you want returned. In this case it is the 4th.

 

HTH
Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com