Firebird Database Split String on Field

Question

Currently working with a Firebird 1.5 database and attempting to pull the data in the correct format natively with SQL.

Consider the following database:

ID | Full Name
 1   Jon Doe
 2   Sarah Lee 

What I am trying to achieve is a simple split on the full name field (space) within a query.

ID | First Name | Last Name
1     Jon          Doe
2     Sarah        Lee 

The issue faced is Firebird POSITION() was introduced in v2.0. Is there any known workaround to split on a space that anyone has come across?

Much appreciate your assistance!

Solution 1

For Firebird 1.5, a solution is to find a UDF that either combines both functions, or provides the position (I don't use UDFs, so I am not sure if one already exists). If none is available, you might have to write one.

The other solution is to write a stored procedure for this functionality, see for example: Position of substring function in SP

CREATE PROCEDURE Pos (SubStr VARCHAR(100), Str VARCHAR(100))
  RETURNS (Pos INTEGER) AS
DECLARE VARIABLE SubStr2 VARCHAR(201); /* 1 + SubStr-lenght + Str-length */
DECLARE VARIABLE Tmp VARCHAR(100);
BEGIN
  IF (SubStr IS NULL OR Str IS NULL)
  THEN BEGIN Pos = NULL; EXIT; END

  SubStr2 = SubStr || '%';
  Tmp = '';
  Pos = 1;
  WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp) DO BEGIN
    SubStr2 = '_' || SubStr2;
    Tmp = Tmp || '_';
    Pos = Pos + 1;
  END

  IF (Str LIKE Tmp) THEN Pos = 0;
END

This example (taken from the link) can be extended to then use SUBSTRING to split on the space.

For searching on a single character like a space, a simpler solution can probably be devised than above stored procedure. For your exact needs you might need to write a selectable stored procedure specifically for this purpose.

However, upgrading your database to Firebird 2.5 will give you much more powerful internal functions that simplify this query (and your life)!

Solution 2

I also wanted to split a full name string to first and last name and I used the following SQL statements in firebird 2.1 Database:

Patients is the table name. The Name field holds the full name string e.g.: "Jon Doe". The FIRST_NAME field will store the first name and the LAST_NAME field the last name

First get the first name (string part before the first space) and execute a TRIM UPDATE statement to remove any spaces.

UPDATE "Patients" SET "Patients".FIRST_NAME = (SUBSTRING("Patients"."Name" FROM 1 FOR (POSITION(' ' IN "Patients"."Name"))))
UPDATE "Patients" SET "Patients".FIRST_NAME = TRIM(BOTH ' ' FROM "Patients".FIRST_NAME)

Then get the last name (the string after the first space) and execute a TRIM UPDATE statement to remove any spaces

UPDATE "Patients" SET "Patients"."LAST_NAME" = (SUBSTRING("Patients"."Name" FROM (POSITION(' ' IN "Patients"."Name")+1)))
UPDATE "Patients" SET "Patients".LAST_NAME = TRIM(BOTH ' ' FROM "Patients".LAST_NAME)

The result will be:

ID | NAME      | FIRST_NAME | LAST_NAME
1    Jon Doe     Jon          Doe
2    Sarah Lee   Sarah        Lee