How to take some value from value in one column in Teradata SQL?

I have table in Teradata SQL like below:

col1
---------
O|1234|5RFTGS|022
O|5526|AAGHT7|022
O|1789|IIT63F|022

​x
 
col1---------O|1234|5RFTGS|022O|5526|AAGHT7|022O|1789|IIT63F|022​

The first value is always: “O|” then there is 4 numbers and so on… I need to create new column only with these 4 numbers code, so as a result I need something like below:

col1              | col2
---------------------------
O|1234|5RFTGS|022 | 1234
O|5526|AAGHT7|022 | 5526
O|1789|IIT63F|022 | 1789

 
col1              | col2---------------------------O|1234|5RFTGS|022 | 1234O|5526|AAGHT7|022 | 5526O|1789|IIT63F|022 | 1789​

How can I do that in Teradata SQL ?

Answer

Use STRTOK

SELECT col1
, STRTOK(col1,'|',2) AS col2
FROM YourTable

 
SELECT col1, STRTOK(col1,'|',2) AS col2FROM YourTable​

Advertisement

Answer