SQLite 字符串函数：REPLACE、SUBSTR、TRIM、ROUND

SQLite 默认情况下配备了 SQLite 库中的一系列内置函数。您还可以使用 C 语言添加其他自定义函数来扩展核心功能。

请注意，对于以下所有示例，您都需要运行 sqlite3.exe 并按如下方式打开与示例数据库的连接

步骤 1)在此步骤中，

1. 打开“我的电脑”并导航到以下目录：“C:\sqlite”，然后
2. 打开“sqlite3.exe”。

第 2 步：使用以下命令打开数据库“TutorialsSampleDB.db”

现在您可以运行任何 查询，并尝试在以下部分使用的任何查询。

查找 SQLite 中字符串的长度

要查找字符串的长度，请使用 LENGTH(X)，其中 X 是字符串值。如果 X 是空值，长度函数将返回空值。

您也可以将 length 函数与数值一起使用，以获取数值的长度。

示例

在下面的示例中，我们将尝试使用以下值的 LENGTH 函数语法

SELECT LENGTH('A string'), LENGTH(NULL), LENGTH(20), LENGTH(20.5);

这将为您提供

结果如下

• LENGTH(‘A string’) 返回 8，这是字符串“A string”的长度。
• LENGTH(NULL) 返回 null。
• LENGTH(20) 返回 2，因为 20 的长度为 2。
• LENGTH(20.5) 返回 4；小数点“.”被计为一个字符，因此您将有四个字符——数字长度的 3 个字符。再加上小数点“.”的一个字符。

使用 UPPER 函数和 LOWER 函数更改大小写

UPPER(X) 将返回相同的 x 字符串，但它将以大写字母表示所有字符串字符。

LOWER(X) 将返回相同的 x 字符串，但它将以小写字母表示所有字符串字符。

如果您将 null 值传递给 UPPER 或 LOWER，它们都将返回 null 值。

如果您将数值传递给 UPPER 或 LOWER，它们两者都将返回相同的数值。

示例

SELECT UPPER('a string'), LOWER('A STRING'), UPPER(20), LOWER(20), UPPER(NULL), LOWER(NULL);

这将为您提供

结果如下

• UPPER(‘a string’) 返回字符串“a string”，并以大写字母表示“A STRING”。
• LOWER(‘A STRING’) 返回字符串“A STRING”，并以小写字母表示“a string”。
• UPPER(20)、LOWER(20) 返回相同的数字，因为它们对数字没有影响。
• UPPER(NULL)、LOWER(NULL) 返回 null，因为我们传递了 null 值给它们。

SQLite SUBSTR 函数

SUBSTR 函数返回一个特定长度的字符串，从指定位置开始。您可以将三个操作数传递给函数，如下所示：“SUBSTR(X,Y,Z)”

• X 是要解析的字符串字面量或字符串列。您可以传递字面值（静态值）或列名，在这种情况下，值将从列的值中读取。
• Y 是要从中减去字符串的起始位置。
• Z 是要从指定位置 Y 开始解析的字符数。此数字是可选的，您可以忽略它，在这种情况下，SQLite 将从位置 Y 开始解析字符串直到字符串的末尾。

示例

在以下查询中，我们将使用 SUBSTR 函数获取学生姓名中从第二个字符开始的 4 个字符。

SELECT StudentName, SUBSTR(StudentName, 2, 4), SUBSTR(StudentName, 2)
FROM Students;

这将为您提供

• SUBSTR(StudentName, 2, 4) 从第二个字符开始对字符串“StudentName”进行子字符串操作，并返回接下来的 4 个字符。
• 但是，在函数 SUBSTR(StudentName, 2) 中，我们没有指定要返回的字符数，因此 SQLite 从第二个位置开始返回“StudentName”中的所有剩余字符。

使用 SQLite REPLACE 函数更改字符串的部分

REPLACE 用于将另一个字符串中的每个字符串替换为另一个字符串。

REPLACE(X, Y, Z) – X 是输入字符串字面量或列。Y 是要用字符串 Z 替换的字符串。请注意，它将替换字符串 Y 的所有出现，而不仅仅是一个。

示例

在以下查询中，我们将用字符串“SQLite”替换所有出现的字符串“xx”。

SELECT REPLACE('xx is very lightweight, xx is easy to learn', 'xx', 'SQLite');

这将为您提供

• replace 函数将所有字符串“xx”替换为字符串“SQLite”。
• 因此，输出将是 “SQLite 非常轻量，SQLite 易于学习”。

使用 SQLite TRIM 函数修剪空格

TRIM 删除字符串开头或结尾的空格。它不会删除字符串中间的任何空格，只删除开头或结尾的空格。

示例

在以下查询中，我们将使用 TRIM 函数删除字符串开头和结尾的空格。

请注意，“||”连接运算符用于在字符串末尾添加额外的“;”。以便在黑框中向您显示已删除的空格。

SELECT TRIM('      SQLite is easy to learn        ') || ';';

这将为您提供

• 请注意，空格如何从字符串的开头和结尾被删除，并在末尾用‘ ; ‘替换。

使用 SQLite ABS 函数读取绝对值

ABS 函数返回数值的绝对值。绝对值是数值，没有正负号。ABS(X) 根据 X 的值返回一个值，如下所示：

• 如果 X 是数值，则返回 X 的绝对值。
• 如果 X 是 null 值，则返回 NULL 值。
• 如果 X 是字符串，则返回“0.0”值。

示例

在以下查询中，我们将尝试使用 ABS 函数获取字符串、数字和 null 值的绝对值。

SELECT ABS(-2), ABS(+2), ABS('a string'), ABS(null);

这将为您提供

结果如下

• ABS(-2)、ABS(+2) 返回 2，因为 2 是“-2”和“+2”的绝对值。
• ABS(‘a string’) 返回“0.0”，因为您传递的是字符串值而不是数值。
• ABS(null) 返回 null，因为您传递了一个 null 值给它。

在 SQLite 中使用 ROUND 函数四舍五入数值

小数是带有浮点数“.”的数字，例如“20.5”、“8.65”。小数点左边的部分称为整数部分，小数点右边的部分称为小数部分。

整数是没有小数点（“.”）的数字。例如，“20”、“8”。

ROUND(X) 将小数或列 X 转换为仅整数。将返回小数点左侧的整数部分，并且将删除小数点右侧的所有数字。

示例

在以下查询中，我们将尝试使用 ROUND 函数的不同选项。

SELECT ROUND(12.4354354), ROUND(12.4354354, 2), ROUND(NULL), ROUND('a string');

这将为您提供

结果如下

• ROUND(12.4354354) – 返回值“12”，因为您没有指定位数。因此，SQLite 删除了所有小数部分。
• ROUND(12.4354354, 2) – 返回值“12.44”，因为您只指定了 2 位数字进行四舍五入，即小数点右边的两位数字。
• ROUND(NULL) – 返回 null，因为您将 null 值传递给它。
• ROUND(‘a string’) – 返回值“0.0”，因为您将字符串值传递给它。

使用 TYPEOF 函数查找表达式的数据类型

如果您想找出列或字面值的数据类型，您可以使用 TYPEOF 函数来做到这一点。

TYPEOF(X) 函数顾名思义，返回表达式 X 的数据类型。它返回一个表示 X 数据类型的字符串值。它根据数据类型返回值，无论是“NULL”值、real、text、integer 等。

示例

在以下查询中，我们将尝试对不同类型的字面值使用 TYPEOF 函数。

SELECT TYPEOF(null), TYPEOF(12), TYPEOF(12.5), TYPEOF('a string');

这将为您提供

返回的结果如下

• TYPEOF(null) – 返回 null，因为您将 null 值传递给它。
• TYPEOF(12) – 返回 integer，因为 12 是一个整数。
• TYPEOF(12.5) – 返回 REAL，因为 12.5 是一个实数。
• TYPEOF(‘a string’) – 返回 text，因为“a string”是文本。

使用 SQLite LAST_INSERT_ROWID 查找最后插入的记录

SQLite 为所有表中的所有行分配一个整数键（Id）。此数字用于唯一标识这些行。

当您在表中插入新行时，SQLite 会为 rowid 分配一个唯一值。

如果表仅在一个列上声明了主键，并且该列的数据类型为 INTEGER，则该列的值将用作 rowid。

LAST_INSERT_ROWID() 函数返回数据库中任何表的最后插入行的 ROW_ID。请注意，它不接受任何操作数。

示例

在以下示例中，我们将按如下方式运行 LAST_INSERT_ROWID() 函数。

SELECT LAST_INSERT_ROWID();

这将为您提供

• LAST_INSERT_ROWID() 返回 0，因为在当前数据库连接的任何表中都没有插入行。

现在，让我们插入一个新学生，并在插入语句后再次运行 LAST_INSERT_ROWID() 函数，如下所示：

INSERT INTO Students VALUES(11, 'guru', 1, '1998-10-12');
SELECT LAST_INSERT_ROWID();

这将为您提供

• 在插入 id 为 11 的新学生后，LAST_INSERT_ROWID() 将返回最后插入的行 id，即 11。

获取 SQLite 库的版本

要获取 SQLite 库的版本，您必须调用 SQLITE_VERSION() 函数。

示例

让我们通过运行以下命令来找出我们正在使用的 SQLite 的版本。

SELECT SQLITE_VERSION();

这将为您提供

SQLITE_VERSION() 返回 3.9.2，这是我们正在使用的 SQLite 的版本。

创建用户定义函数和聚合用户定义函数

对于某些用户来说，SQLite 的核心功能是有限的。他们可能需要添加自定义函数来满足其自定义需求。

SQLite 不支持创建用户定义函数。与其他数据库管理系统不同，您不能直接在 SQLite 中创建用户定义函数。

但是，您可以使用 C#、C、PHP 或 C++ 等编程语言创建函数，并使用“sqlite3_create_function”函数将其附加到 SQLite 库本身的核心 SQLite 函数中。然后您可以在数据库中重用它们。

示例

在以下示例中，我们将使用 C# 编程语言创建一个用户定义函数，并将其添加到 SQLite 函数中。

[SQLiteFunction(Name = "DoubleValue", Arguments = 1, FuncType = FunctionType.Scalar)]
public class DoubleValue: SQLiteFunction
{
    public override object Invoke(object[] args)
    {
        return args[0] * 2;
    }
}

此代码片段是用C# 编程语言编写的；它使用 C# 代码创建了一个 C# 函数。

函数名称将是“DoubleValue”，它接受一个参数并返回其值乘以 2。

请注意，C# 会自动将此函数添加到 SQLite。您需要做的就是编译并运行此代码。然后 C# 会将同名函数添加到 SQLite 函数列表中。然后您可以在 SQLite 中使用此函数。

通过 C#、C、PHP 或 C++ 等编程语言，您还可以创建聚合用户定义函数。这些函数用于扩展 SQLite 中的聚合函数，并使用它们创建自定义聚合函数。

摘要

• SQLite 提供了一套全面的内置函数，正如我们所见，这些函数使得处理数据库表和列变得容易。您可以在 SQL 查询中的任何表达式中使用这些函数以及列和字面值。
• 您也可以将 length 函数与数值一起使用，以获取数值的长度。
• 如果您将 null 值传递给 UPPER 或 LOWER，它们都将返回 null 值。
• SUBSTR 函数返回一个特定长度的字符串，从指定位置开始。
• REPLACE 用于将另一个字符串中的每个字符串替换为另一个字符串。
• TRIM 删除字符串开头或结尾的空格。
• ABS 函数返回数值的绝对值。
• ROUND(X) 将小数或列 X 转换为仅整数。
• TYPEOF(X) 函数顾名思义，返回表达式 X 的数据类型。
• LAST_INSERT_ROWID() 函数返回数据库中任何表的最后插入行的 ROW_ID。