Firebird Database Split String on Field

firebird, sql

Solution

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)!

Problem

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!

Original source