Firebird数据库分裂字符串在字段上-FirebirdDatabaseSplitStringonField

3  

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.

对于Firebird 1.5,解决方案是找到一个UDF,它既可以组合两个函数,也可以提供位置(我不使用UDF,所以我不确定是否已存在)。如果没有,你可能需要写一个。

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

另一种解决方案是为此功能编写存储过程,例如参见: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.

此示例(取自链接)可以扩展为然后使用SUBSTRING在空间上拆分。

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

但是,将数据库升级到Firebird 2.5将为您提供更强大的内部功能,以简化此查询(以及您的生活)!

0  

You could use a UDF, but that isn't strictly SQL

您可以使用UDF,但这不是严格的SQL

you could write a stored procedure to parse and split but thats not strictly SQL either

你可以编写一个存储过程来解析和拆分,但这不是严格的SQL

0  

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:

我还想将全名字​​符串拆分为名字和姓氏,我在firebird 2.1数据库中使用了以下SQL语句:

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

患者是表名。 Name字段包含全名字符串,例如:“Jon Doe”。 FIRST_NAME字段将存储名字,LAST_NAME字段将存储姓氏

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

首先获取第一个名称(第一个空格之前的字符串部分)并执行TRIM UPDATE语句以删除任何空格。

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

然后获取姓氏(第一个空格后面的字符串)并执行TRIM UPDATE语句以删除任何空格

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