9.9 Split string using comma as the delimiter
Use comma as the delimiter to split a string into a sequence of strings.
Get names of products each customer purchases. Use the comma to separate multiple products. Here are Product table and Sales table:
Product：
| ID | Name | Website |
|---|---|---|
| R | Report | http://www.raqsoft.com.cn/r |
| P | esProc | http://www.raqsoft.com.cn/p |
| C | esCalc | http://www.raqsoft.com.cn/c |
| M | AI Models | http://www.yimming.com/ |
| … | … | … |
Sales：
| ID | Customer | Product |
|---|---|---|
| 1 | VINET | R |
| 2 | TOMSP | P,R |
| 3 | HANAR | P,R,C |
| 4 | VICTE | P |
| … | … | … |
The s.split(d) function works with @c option to split a string character by character when parameter d is absent.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query(“select * from Product”) |
| 3 | =A1.query@x(“select * from Sales”) |
| 4 | =A3.run(Product=Product.split@c()) |
| 5 | =A4.run(Product=Product.(A2.find(~).Name).concat@c()) |
A1 Connect to the database.
A2 Import Product table.
A3 Import Sales table.
A4 split() function works with @c option to split products in Sales table with the comma and return result as a sequence.
A5 Find product names according to product ID and connect members of the sequence of product names into a string.
Execution result:
A4:
| ID | Customer | Product |
|---|---|---|
| 1 | VINET | [R] |
| 2 | TOMSP | [P,R] |
| 3 | HANAR | [P,R,C] |
| … | … | … |
A5:
| ID | Customer | Product |
|---|---|---|
| 1 | VINET | Report |
| 2 | TOMSP | esProc,Report |
| 3 | HANAR | esProc,Report,esCalc |
| … | … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
