Extract parts of a string

You might run into a scenario where you have a column that has pieces of information you’d like to extract separated by a character of some sort. Through Visual SQL steps, you have the ability to extract the sub-string to the left or right of the delimiting character.

In our example, we have a column called “Project Key/Name” that has our project key and project name information, where the project key and the project name are separated by a hyphen:

Let’s say we want to parse the string to only have the project name in a separate column. To do this, we turn to a Visual SQL step to help transform the initial string. First, we need to use a “Formula column” step, choose Custom as the formula type, then use the following formula:

Let’s try to breakdown what’s happening in this formula. First, we’ll look at the SUBSTR function. This function extracts a substring from a string. There are three parameters for the function, SUBSTR(1,2,3).

• Parameter 1 is the column name containing the initial string.

• Parameter 2 is an integer representing the starting position of the substring to extract. This can be at the beginning of the string, the middle, or even towards the end of the string. To set the position, you just need to know the numerical position of where you’d like to start the extraction.

• Parameter 3 is also an integer, and it represents the position of where our extracted substring ends in the initial string.

In parameter 2 of our SUBSTR function, we use the INSTR function. INSTR() returns the position of a character in a string. There are two parameters for INSTR function, INSTR(A,B).

• Parameter A is the column containing the string which will be searched.

• Parameter B is the character(s) you’re searching for. The output is the position of the first instance of the character in the string, which is why the INSTR function is in parameter 2 of the SUBSTR function. In our example, the function INSTR("Project Key/Name", '-')+2 searches for the position of the hyphen character, '-', within the strings in our “Project Key/Name” column. The +2 is to return the position two characters to the right of the hyphen, in order to not include the hyphen or extra space to the right of the hyphen in our new column.

Finally, for parameter 3, we use the LENGTH function, which just calculates the length of a string. Since we want to get the entire project name for each row, and each project name length varies, we can account for this by using the LENGTH function. This returns the last character’s position in the string for each varying project name.

Running the full formula (and renaming the Formula column to “Project name extracted”) outputs the following:

We can also extract the Project key abbreviation using the following formula:

We’ll quickly go through this example.

• Parameter 1 remains the same as our first example, since we’re extracting a substring from the same initial string.

• Parameter 2 is simply a 1 to indicate our substring starts at the first position of our initial string.

• We use the INSTR function again, but this time we use it in parameter 3. We want to get the position of the character just before the hyphen, so we search for the hyphen character, get its position from the INSTR function, then subtract 2 from that position to get the end position of our substring.

Running the full formula (and renaming the Formula column to “Project key extracted”) outputs the following: