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

I have table in Teradata SQL like below:

col1
---------
O|1234|5RFTGS|022
O|5526|AAGHT7|022
O|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

How can I do that in Teradata SQL ?

CodePudding user response：

Use STRTOK

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

CodePudding user response：

Your example data shows fixed length:

Substring(col1 From 1 FOR 13),
Substring(col1 From 15)

But it's probably variable, then INSTR can be used to find the 3rd '|':

Substring(col1 From 1 FOR Instr(col1, '|', 1, 3)-1),
Substring(col1 From Instr(col1, '|', 1, 3) 1)

If there might be less than 3 | you need to add a sanity check using CASE (or switch to REGEXP_SUBSTR).