在sqlite中拆分名字和姓氏 [英] Split FirstName and LastName in sqlite

问题描述

我在 sqlite db 中有一个名为 [tblbook] 的表，其中有一列 [authors].我试图在 sql 中做的是将作者值拆分为名字和姓氏，并按姓氏对其进行排序.我确实找到了这个很棒的代码:

I have a table in sqlite db called [tblbook] with a column [authors]. What I am trying to do in the sql is to split the author values to firstname and the lastname and sort it on lastname. I did find this great code:

 SELECT substr(BookAuthor, 1, NULLIF(CHARINDEX(' ', BookAuthor) - 1, -1)) AS [FirstName],
   substr(BookAuthor, CHARINDEX(' ', BookAuthor) + 1, LEN(BookAuthor)) AS [LastName]
 FROM tblBook where _id=3

它在 MSSQL 上运行良好，但 sqlite 没有 charindex 函数，因此它失败了.

It works perfectly on MSSQL but sqlite doesn't have the charindex function hence it fails.

任何人都可以请善待并告诉我实现这一目标的最佳方法是什么.

Could anyone please be kind and advise me what should be the best approach to achieve this.

推荐答案

遗憾的是 SQLite 缺少此功能:

Unfortunately this functionality is missing from SQLite:

• http://www.sqlite.org/lang_corefunc.html

SQLite3 中子字符串的索引?

也许您可以使用 http://www.sqlite 将自定义字符串位置函数提供给 SQLite.org/c3ref/create_function.html

但如果您真的需要它，有一个复杂且无效的解决方法:

But if you really need it, there is a complex, ineffective workaround:

http://sqlfiddle.com/#!7/e03a4/3

1:创建一个数字表/视图

1: create a numbers table/view

2:将作者加入数字表，并选择空格的最小位置

2: join authors to numbers table, and choose the MIN position of the space

3:现在您可以拆分名称

3: now you can split the names

SELECT
  substr( name, 1, pos-1) AS first_name,
  substr( name,    pos+1) AS last_name
FROM (
      SELECT
        author.name,
        numbers.x AS pos
      FROM       author
      INNER JOIN numbers
      WHERE substr( author.name, numbers.x, 1) = ' '
      GROUP BY author.name
     ) AS a
ORDER BY last_name;

这篇关于在sqlite中拆分名字和姓氏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！