9.4 Enclose each string member with quotation marks during concatenation
Concatenate members of a sequence into a string while enclosing each member with quotation marks.
Find which states employees of each department are based, and separate state names with the space. As certain state names contain the space, we need to enclose each state name with quotation marks to distinguish them. Below is Employee table:
| ID | NAME | SURNAME | STATE | DEPT | SALARY |
|---|---|---|---|---|---|
| 1 | Rebecca | Moore | California | R&D | 7000 |
| 2 | Ashley | Wilson | New York | Finance | 11000 |
| 3 | Rachel | Johnson | New Mexico | Sales | 9000 |
| 4 | Emily | Smith | Texas | HR | 7000 |
| 5 | Ashley | Smith | Texas | R&D | 16000 |
| … | … | … | … | … | … |
In SPL, A.concat() function works with @q option to enclose each string members with double quotation marks at concatenation. Similarly, the function uses @i option to enclose each string member with single quotation marks at concatenation.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from Employee”) |
| 3 | =A2.group(DEPT; ~.id(STATE):STATES) |
| 4 | =A3.new(DEPT, STATES.concat@q(" "):STATES) |
A1 Connect to the data source.
A2 Import Employee table.
A3 Group Employee table by department and get distinct state names for each group.
A4 Use A.concat() function to concatenate state names of each group into a string, during which @q option encloses each member with double quotation marks.
Execution result:
| DEPT | STATES |
|---|---|
| Administration | “Florida” “Pennsylvania” |
| Finance | “California” “Colorado” “Florida” “Georgia” “Illinois” “Michigan” “New Jersey” “New York” “North Carolina” |
| … | … |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
