An example of a with RECURSIVE application in Teradata

Source: Internet
Author: User
Tags trim

Requirement: Splits a string that uses a delimiter connection for a fixed number of digits, such as splitting a record 215|482|850|870 into 215,482,850,870 four records
Result: The WITH RECURSIVE statement is capable of implementing this function and is tested as follows:
CREATE TABLE Dwsdata.gift_info (
gf_id varchar (50),
Channels varchar (100)
);

SELECT * from Dwsdata.gift_info;
GF_ID Channels
10710701010201002 215|482|850|870

with RECURSIVE temp_table (gf_id,channels) as
(
Select
 gf_id
 ,trim (Channels)
from Dwsdata.gift_info
where channels<> '
UNION ALL
select 
 gf_id
 ,substr ( channels,5)
from  temp_table aa
where   char (aa.channels) <>3
)
Select
  TRIM (gf_id)
 ,substr (channels,1,3)
from   temp_table
;
Result:
gf_id             channels
10710701010201002 215
10710701010201002 482
10710701010201002 850
10710701010201002  870

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.