9.18 Parse table field values according to a regular expression
Parse values of the character type field according to a regular expression.
One task is to get road number (the numerical digit) form each Address value. Below is part of data in Customer table:
| ID | Name | City | Address |
|---|---|---|---|
| 1 | VINET | Beijing | 124 Guangming North Road |
| 2 | TOMSP | Jinan | 543 Qingnian East Road |
| 3 | HANAR | Qinhuangdao | 22 Guanghua Street |
| 4 | VICTE | Nanjing | Qinglin bridge 68 |
| … | … | … | … |
In SPL, S.regex(rs) function searches for a matching string within string S according to regular expression rs and returns null if no match is found.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from Customer”) |
| 3 | =A2.run(Address=number(Address.regex(“\D*(\d+)\D*”)(1))) |
A1 Connect to the data source.
A2 Import Customer table.
A3 S.regex() gets road number from each address and parses it as a number.
Execution result:
| ID | Name | City | Address |
|---|---|---|---|
| 1 | VINET | Beijing | 124 |
| 2 | TOMSP | Jinan | 543 |
| 3 | HANAR | Qinhuangdao | 22 |
| … | … | … | … |
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
