extracting values

Database

extracting values

Hi

I have following dataset that would like to extract first only 5 digits from last set of group using teradata syntax. 

The address is one field and is delimited by space, and the last set of number always represents zip code (5 or 5 + 4)  Thanks

123 Main St Los Angeles CA 92345777   

San Francisco CA 123456789

New York NY 24165

123 West Lake Ave FL 23456

Want :

----> 92345

       12345

       24165

       23456

11 REPLIES
Senior Apprentice

Re: extracting values

If it's always delimited by spaces you can utilize INSTR to find the last space and SUBSTRING to return the following characters: 

SUBSTRING(col FROM INSTR(col, ' ', -1, 1) +1 FOR 5)

Re: extracting values

Hi dnoeth,

Thank you so much for your answer, but I am getting error with following code.  

Select substring (col_name from INSTR(col_name ' ', -1, 1) + 1 FOR 5 ) from tbl_name ; 

Is "INSTR" teradata syntax ? It doesn't indicate it as syntax when typing "INSTR" teradata studio... 

Thanks

Senior Apprentice

Re: extracting values

What's your TD release?

INSTR is supported since TD14.

Enthusiast

Re: extracting values

  Hi dnoeth,


                Can you please answer to this Question?

SOURCE :                                                 TARGET  SHOULE BE LIKE THIS :
EMP | PHONE EMP | PH1 | PH2
A | 234565 A | 234565 | 314568
A | 314568 B | 435689 | 567438
B | 435689 C | 657845 | 765432
B | 567438
C | 657845
C | 765432

Thanks,

siva.

Enthusiast

Re: extracting values

Hi dnoeth,

                   one more task is here.

source : 

     EMP_ID  EMP_NAME       SAL_MONTH      SAL
----------- ---------- ----------- -----------
1 RAJ 1 30000
1 RAJ 2 35000
1 RAJ 3 10000
1 RAJ 4 30000
1 RAJ 5 25000
1 RAJ 6 27000
2 JOHN 1 22000
2 JOHN 2 11000
2 JOHN 3 13000
2 JOHN 4 31000
2 JOHN 5 18000
2 JOHN 6 22000


Target :

EMP_ID         EMP_NAME   CURRENT_MONTH                        CURRENT_SAL       PREV_SAL
----------- ---------- ----------- ----------- --------
1 RAJ 1 30000 0
1 RAJ 2 35000 30000
1 RAJ 3 10000 35000
1 RAJ 4 30000 10000
1 RAJ 5 25000 30000
1 RAJ 6 27000 25000
2 JOHN 1 22000 0
2 JOHN 2 11000 22000
2 JOHN 3 13000 11000
2 JOHN 4 31000 13000
2 JOHN 5 18000 31000
2 JOHN 6 22000 18000

thanks,

siva.

Re: extracting values

Hi Dnoeth, It is TD 14.10, but INSTR doesn't color coded as SQL syntax in teradata studio.. Is there any alternative way to solve this logic ? Thanks 

Senior Apprentice

Re: extracting values

It doesn't matter if INSTR is highlighted in Studio, if you're on TD14.10 it's supported.

You simply got a syntax error, there's a missing comma:

substring (col_name from INSTR(col_name, ' ', -1, 1) + 1 FOR 5 )
Senior Apprentice

Re: extracting values

Hi Siva,

please post new questions as a new topic.

Q1 is a simple aggregate:

select emp, min(phone), max(phone)
from tab
group by emp

Q2 needs LAG, which is not implemented in Teradata, but can easily be rewritten:

coalesce(min(sal) 
over (partition by emp_id
order by sal_month
rows between 1 preceding an 1 preceding), 0)

Re: extracting values

Hi dnoeth,

Thanks for the code. It worked with teradata sql assistant software, but not with Teradata Studio. 

one more question :

How do I just remove last 4 digit rather than extracting only first 5 digits from the address field ? Thanks

For example,

123 Main St Los Angeles CA 923457777    -- > 123 Main St Los Angeles CA 92345

San Francisco CA 123456789  --->  San Francisco CA 12345

New York NY 24165   --> New York NY 24165

123 West Lake Ave FL 23456  ---> 123 West Lake Ave FL 23456