MacLochlainns Weblog

There are two built in functions that let you parse dynamic substrings from the left of a string. They are the LEFT and FIND functions. The LEFT function actually does the parsing but the FIND function lets you dynamically find a delimiting character, like a space.

Assuming cell A1 holds the value of Joseph F. Smith, you can parse Joseph by using a static value of seven for the first white space in the string. This works because each character maps to a string, and strings start with the number one. The LEFT function supports three formal parameters, the first is the string you’re parsing, the second is where to stop, and the third is where to start. The position you start at is assumed to be one, which makes the last parameter an optional parameter. You only provide the start with parameter when you want to start someplace other than the left most position.

This is the simplest use of the LEFT function with its two required parameters.

=LEFT(A1,7)

It returns the substring Joseph from the string Joseph F. Smith, which is stored in cell A1.

The simplest solution merely illustrates a concept. You’re not going to do this unless you have a bit more data. Naturally, the list of first names have different lengths in the real world. You must include the FIND function inside the call to the LEFT function to make your function dynamic. The FIND function lets you capture the position of a white space delimiter.

The FIND function supports three formal parameters, the first is the substring you’re looking for in the string, the second is the string to search, and the third is where to start. Like the LEFT function, the position you start at is assumed to be one, which makes the last parameter an optional parameter. You only provide the start with parameter when you want to start someplace other than the left most position. While not required, you should consider always providing the start with parameter to the FIND function.

The following shows you how to use the FIND function to locate the first occurrence of a white space in a string stored in cell A1.

=FIND(" ",A1,1)

You can now make you’re LEFT function dynamic by putting the FIND function inside it. As qualified, the FIND function looks for the first occurrence of a white space, and returns it into the second parameter of the LEFT function. The sample formula now has a nested function, which makes it a mega formula in Microsoft Excel parlance. The following sample also includes the optional start with parameter for clarity.

=LEFT(A1,FIND(" ",A1,1),1)

The problem with the foregoing solution occurs when a name in the list doesn’t have a middle initial or name, or last name. The lack of a second name means that there won’t be any white space between to substrings in the base string. This situation causes the logic to fail because the FIND function returns a #VALUE! error when it can’t find a white space in the string.

You can prevent the error by wrapping the nested FIND function and LEFT function inside two IFERROR functions. This IFERROR function returns the positional value of the FIND function, or an alternative copy of the base string.

=IFERROR(FIND(" ",A1,1),A1)

This type of logic inside the LEFT function causes the LEFT function to fail when the base string is returned to it instead of a position number. You must wrap the LEFT function inside another IFERROR function to guarantee that you don’t throw an error. This also lets you return the base string as the valid substring when appropriate.

=IFERROR(LEFT(A1,IFERROR(FIND(" ",A1),A1)-1),A1)

Assuming you put this formula in cell C1, you could copy this relative reference formula down in a column. It would require that you had a list of full names that followed a first name, white space, middle initial or name, white space, and last name in the A column.

There are three built in functions that let you parse dynamic substrings from the right of strings. They are the RIGHT and FIND functions that you may have covered when reading how to parse from the left. The LEN function is the other function, and it lets you find the length of a string. Together these functions lets you find the length of a substring on the right.

The RIGHT function actually does the parsing but the FIND and LEN functions let you dynamically find where to cut a substring out of a base string. This example continues to use the string Joseph F. Smith.

The RIGHT function has only two required parameters. The first parameter is the string that you’re parsing. The second parameter is the length of the substring. If you inspect the string, Smith is only five characters long. A static call to the RIGHT function is shown below.

=RIGHT(A1,5)

It returns the substring Smith from the string Joseph F. Smith, which is stored in cell A1.

Like the LEFT function example, this static approach to parsing merely illustrates a concept. You’re not going to do this with real data because the list of first names have different lengths in the real world. You must include the FIND function twice inside your call to the RIGHT function because you’re parsing the string based on the second occurrence of a white space in the string. While the FIND function lets you dynamically capture the position of the white space delimiter, a nested FIND function lets you capture the correct start with parameter value. That value is one position after the first occurrence of a white space.

The FIND function supports three formal parameters, the first is the substring you’re looking for in the string, the second is the string to search, and the third is where to start. Like the LEFT function, the position you start at is assumed to be one, which makes the last parameter an optional parameter. You only provide the start with parameter when you want to start someplace other than the left most position. While not required, you should consider always providing the start with parameter to the FIND function. It improves readability.

The following shows how to use a FIND function to locate the position one beyond where the first occurrence of a white space is found in a string.

=FIND(" ",A1,1)+1

If you pass the preceding FIND function call as the start with parameter to another FIND function, you can locate the second occurrence of a white space in a string stored in cell A1. Nesting function calls inside functions creates what are known as mega formulas in Excel. The following demonstrates a mega formula to find the second instance of a white space in a string.

=FIND(" ",A1,FIND(" ",A1,1)+1)

This returns the value of ten. You now know where to start but not the length of the substring on the right. You first need to find the length of the total string. You use the LEN function to find that, like the following.

=LEN(A1)

The LEN function returns fifteen. You can calculate the length of the substring as five by subtracting the position of the second white space from the length of the string. While you could inspect that value in this one cell, you can’t do that when there are one hundred or one hundred thousand names in a list. The way to dynamically capture the right hand side substring length is shown below.

=LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1)

Now that you know how to capture the length of the substring, you can create a larger mega forumla to parse the substring on the right from the base string. The working example follows below.

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+1))

The problem with the foregoing solution is that it fails when one of the names in the list doesn’t have a middle initial or name. The failure occurs because there would only be one white space in the base string, and the logic expects two. The FIND function looking for the second white space returns a #VALUE! error.

You can prevent this error by wrapping the nested FIND function calls and RIGHT function with calls with an IFERROR function. An IFERROR function returns the positional value of the FIND function, or an alternate value, like a fresh copy of the base string.

=IFERROR(RIGHT(A1,LEN(A1)-IFERROR(FIND(" ",A1,FIND(" ",A1)+1),FIND(" ",A1))),A1)

The IFERROR function inside the RIGHT function can cause the RIGHT function to fail when a base string is returned instead of a position number. Therefore, you must also wrap the RIGHT function inside another IFERROR function to avoid an error. This guarantees the return of the base string as a valid substring.

Assuming you put this formula in cell C1, you could copy this relative reference formula down in a column. It would require that you had a list of full names that followed a first name, white space, middle initial or name, white space, and last name in the A column.

There are three built in functions that are required to let you parse dynamic substrings from the middle of base strings. Two of them are the MID and FIND functions. You’ve seen how to use the FIND function in the left and right parsing examples of this blog. The third function is the IFERROR function, which is required when a middle string doesn’t exist. There are two more functions that let you trap for the possibility of a single base string. They are the IF and ISNUMBER functions.

The MID function takes three required parameters. The first is the text value or cell reference, the second is the start with value, and the third is the length of the substring. Dynamic substrings require you to bracket them, which means you need to find their beginning and ending positions and measure their length.

This basic idea means you parse the middle string from a set of three strings by finding their delimiters. As in the other examples, you’ll work with the string Joseph F. Smith as a base string. First, you find the first character of the middle string. You do this by finding the position of the first delimiting white space with a FIND function, and then you add one to the returned result value. The example is below.

=FIND(" ",A1,1)+1

Next, you find the position of the second delimiter. This requires that you create what is known as a mega formula, which you create by nesting one or more formulas in another. You can use the following formula to do that.

=FIND(" ",A1,FIND(" ",A1)+1)

You can then calculate the length of the middle string by subracting the first result from the second one, as shown below:

=FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1

After you’ve mastered those formulas, you need to create a mega formula with the MID function. This doesn’t have any error trapping yet, so it is very dependent on data that contains three substrings separated by white spaces.

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)

The preceding function returns F.. If the middle string were a middle name, it would return the middle name. It fails when you have a base string that lacks three substrings. You need to wrap the nested FIND function and MID function inside two respective IFERROR functions. An IFERROR function returns the positional value of the FIND function, or an alternate value, like a fresh copy of the base string or a numeric equivalent. The following example uses an IFERROR function call to substitutes a zero value because the absence of a second white space means there isn’t a middle string.

=MID(A5,FIND(" ",A5)+1,IFERROR(FIND(" ",A5,FIND(" ",A5)+1)-FIND(" ",A5)-1,0))

A second IFERROR wrapping the MID function lets you return a null value for middle name when there is only one name in the base string, like Joseph.

=IFERROR(MID(A5,FIND(" ",A5)+1,IFERROR(FIND(" ",A5,FIND(" ",A5)+1)-FIND(" ",A5)-1,0)),"")

Assuming you put this formula in cell D1, you could copy this relative reference formula down in a column. It would require that you had a list of full names that followed a first name, white space, middle initial or name, white space, and last name in the A column.

This framework is predicated on two assumptions. You are migrating from a MySQL to Oracle database, and your external code doesn’t enforce case sensitivity on inputs because it once relied on the MySQL ENUM data type. Oracle check constraints enforce case sensitive inputs, which may be a solution in itself during migration. At least, it is a solution when you handle case sensitive errors gracefully in your external code. Assuming you want an alternative to changing your external application code and you want to avoid throwing errors, here are the steps to do so.

Create testing environment

Create a testing environment that uses a VARCHAR2 column without a database level constraint in conjunction with a database trigger. You can do this because a before insert or update trigger will enforce the equivalent of a database constraint. The trigger lets you define the behavior of the constraint.

Here’s the setup code, which relies on a small item_type lookup table:

-- Conditionally drop table and sequence before attempting to create them.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN (UPPER('item_type')
                                 ,UPPER('item'))) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN (UPPER('item_type_s1')
                                    ,UPPER('item_s1'))) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create item_type table, index on the natural key, and sequence; then seed data.
CREATE TABLE item_type
( item_type_id NUMBER        CONSTRAINT pk_item_type   PRIMARY KEY
, TABLE_NAME   VARCHAR2(30)  CONSTRAINT nn_item_type_1 NOT NULL
, column_name  VARCHAR2(30)  CONSTRAINT nn_item_type_2 NOT NULL
, type_name    VARCHAR2(30)  CONSTRAINT nn_item_type_3 NOT NULL
, code         VARCHAR2(5)
, meaning      VARCHAR2(255) CONSTRAINT nn_item_type_4 NOT NULL);
 
CREATE UNIQUE INDEX item_type_u1
  ON item_type(TABLE_NAME,column_name,type_name);
 
CREATE TABLE item
( item_id        NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_type      NUMBER        CONSTRAINT nn_item_1  NOT NULL
, item_title     VARCHAR2(60)  CONSTRAINT nn_item_2  NOT NULL
, item_rating_id NUMBER        CONSTRAINT nn_item_3  NOT NULL);
 
CREATE SEQUENCE item_type_s1 START WITH 1001;
CREATE SEQUENCE item_s1 START WITH 1001;
 
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','DVD',NULL,'DVD');
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','VHS',NULL,'VHS');
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','Blu-ray',NULL,'BLRY');

As you may have noticed, the seeded data contains case sensitive strings. The absence of a database-level constraint on a column of a table requires a database trigger. The trigger acts as an alternative to a check constraint and lets you enforce a filtered insertion rule.

Oracle lets you write logic directly into a database trigger, but it is better to write the logic into a stored function or procedure and call it from the trigger’s execution block.

Create a stored function

The stored function takes a string, looks it up in the item_type table, and returns the correct case-sensitive string. The logic is implemented in this function:

CREATE OR REPLACE FUNCTION proper_item_type
( item_type VARCHAR2 ) RETURN VARCHAR2 IS
  CURSOR c (type_name_in VARCHAR2) IS
    SELECT type_name
    FROM   item_type
	  WHERE  UPPER(type_name) = UPPER(type_name_in);
BEGIN
  FOR i IN c (item_type) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

Create a database trigger

After you’ve defined the logic for the trigger, you need to define the database trigger. The following creates a before insert or update database trigger on the item table. The trigger ensures that INSERT statements to the item table store case sensitive copies from the list of possible values found in the item_type lookup table.

CREATE OR REPLACE TRIGGER proper_item_type_t1
  BEFORE INSERT OR UPDATE ON item
  FOR EACH ROW
BEGIN
  :NEW.item_type := proper_item_type(:new_item_type);
END;
/

The trigger takes a non-compliant or compliant string and always return a compliant string before anything is put in the item table.

Test the framework

You can test the framework by inserting an uppercase string BLU-RAY and evaluating the actual results. The following INSERT statement tests the outcome of inserting a non-compliant string.

INSERT INTO item VALUES (item_s1.nextval, 'BLU-RAY', 'Star Wars I', 1001);

You’ll see that a compliant string was actually inserted because the trigger called the function, and the function changed the value from a non-compliant to compliant string. This query let’s you see the result:

SELECT * FROM item;

It inserted Blu-ray in the item_type column.