...

9.20 Parse table field values using code

 
spl-cookbook(224)

Use code to parse a character type filed of the table sequence.
Calculate average salary of employees born in 1980s. Their ages need to be extracted from IDs. Below is part of the Employee table:

ID Name Identification Salary
1 Rebecca Driving license:495319197411204628 7000
2 Ashley ID number:103263198007194980 11000
3 Rachel ID number:721125197012173641 9000
4 Emily ID number:619124198503071617 7000
5 Ashley ID number:248238197505138795 16000
… … … …

We cannot handle this computing task using only one function. Instead, we can parse and process strings step by step using multiple functions.

SPL script:

A
1 =connect(“db”).query@x(“select * from Employee”)
2 =A1.run(Identification=Identification.regex(“\\D*(\\d+)”)(1))
3 =A2.run(Identification=mid(Identification,7,4))
4 =A3.run(Identification=number(Identification))
5 =A4.select(Identification>=1980 && Identification <=1989)
6 =A5.avg(Salary)

A1 Connect to the data source and import Employee table.
A2 Use S.regex() function to read the number part of the Identification value.
A3 Use mid() function to read from digit 7 to digit 10 of the ID number, which is the birthyear.
A4 Use number() function to parse the year string as a number.
A5 Get records of employees born in 1980s.
A6 Calculate average salary of the selected employees.

Execution result:

ID
7256.16
spl-cookbook(224)
 
 •  221 View  •