ACL Character Function – Split() Function

The ACL Audit Audit Command Language is somewhat simpler than most other scripting language like SAS or SQL since it allows for a lot of the analysis to be done via  the GUI the tool provides. However, since it is targeted towards certain kind of specific projects, specifically audit related, these compromises are acceptable. However, some of the more complex features of the competing scripting tools.

These features are specific functions that allow for quick formatting and transformation of data for specific complex situations. Some of these functions are ‘Split()‘, ‘Between()‘, ‘Match()‘. In this post of the series ‘Audit Command Language tutorial for Beginners’, we will be discussing the Split() function.

The Split function is used to extract a certain segment from a character function based on a certain character or special character. We will consider the example of a date field to demonstrate the use of this function because it has a special character “/” to split the day, month & year into three clear segments. The function takes as input, the separator and the position to be extracted to give the resulting column with the required segment.

Syntax:SPLIT(string, separator, segment <,text_qualifier>)

Example in a script to create a new field using Split Function

split-script

DELETE FIELD TEST_Field1 OK
DEFINE FIELD TEST_Field1 COMPUTED AS
SPLIT(TEST_FIELD,”/”,2)

Example in a workspace to create a new field using Split Function

split-workspace

TEST_FIELD1 COMPUTED
SPLIT(TEST_FIELD,”/”,2)

The result of the above statements using the Split function is show in the screenshot below:

split-result

In the above example, we are converting a date value into a character value and then applying the Split function in order to extract the month day segment of the date value i.e. number stored as a character in the new column Test_Field1. The above example is a 2-step process to show the implementation of the function. Otherwise, if the date field is imported into ACL as character field, the Split function can be used directly to extract any of the three segments created by the special character “/”. You can see a demonstration of the function in the video below:

Please keep practicing and feel free to reach out to us with your valuable feedback and comments. Please go through the website to review ACL script examples and ACL script commands sign up for our newsletter, so that we may keep you posted on the latest activity on our website and Youtube channel.

.