server函数调研：split函数

旧日寒暄

已于 2023-12-18 15:12:07 修改

阅读量612

点赞数 11

分类专栏： server函数开发文章标签： sql c语言数据库

于 2023-12-13 10:45:59 首次发布

本文链接：https://blog.csdn.net/qq_51086008/article/details/134966196

版权

server函数开发专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1. 基本信息

（基于SQL Server的STRING_SPLIT函数）

1.语法

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

2.参数

string

任何字符类型（例如 nvarchar、varchar、nchar 或 char）的表达式。

separator

任何字符类型（例如nvarchar(1)、varchar(1)、nchar(1) 或 char(1)）的单字符表达式，用作串联子字符串的分隔符。

enable_ordinal

一个 int 或 bit 表达式，用作启用或禁用 ordinal 输出列的标志。

如果值为 1，则启用 ordinal 列。

如果省略 enable_ordinal 、为 NULL 或值为 0，则禁用 ordinal 列。

3.返回类型

如果未启用 ordinal 输出列，STRING_SPLIT 将返回一个单列表，其中的行即子字符串。列的名称为 value。如果任何输入参数为 nvarchar 或 nchar，则它返回 nvarchar 。否则，将返回 varchar。返回类型的长度与字符串参数的长度相同。

如果 enable_ordinal 参数传递的值为 1，则返回第二个名为 ordinal 的列，其中包含每个子字符串在输入字符串中的位置（从 1 开始的索引值）。返回类型为 bigint。

4.综述

STRING_SPLIT 输入一个包含分隔子字符串的字符串，并输入一个字符用作分隔符。根据需要，函数还支持值为 0 或 1 的第三个参数，该参数分别禁用或启用了 ordinal 输出列。

STRING_SPLIT 输出一个单列表或双列表，具体取决于 enable_ordinal 参数。

如果 enable_ordinal 为 NULL、被省略或值为 0，STRING_SPLIT 将返回一个单列表，其中的行包含子字符串。输出列的名称为 value。
如果 enable_ordinal 的值为 1，该函数将返回一个包含两列的表，其中 ordinal 列由原始输入字符串中从 1 开始的子字符串的索引值组成。

5.注意

请注意，enable_ordinal 参数必须是常数值，而不能是列或变量。它还必须是值为 0 或 1 的 bit 或 int 数据类型。否则，此函数将引发错误。

输出行可以按任意顺序排列。顺序不保证与输入字符串中的子字符串顺序匹配。可以通过使用 ORDER BY 子句（在 SELECT 语句中）覆盖最终排序顺序，例如 ORDER BY value 或 ORDER BY ordinal。

0x0000 (char(0)) 是 Windows 排序规则中未定义的字符，无法包括在 STRING_SPLIT 中。

6.举例：

U. 两个或多个连续出现的分隔符字符（✓）

此时将出现长度为零的空子字符串。空子字符串的处理方式与普通子字符串相同。可以通过使用 WHERE 子句筛选出包含空的子字符串的任何行，例如 WHERE value <> ''。如果输入字符串为 NULL，则 STRING_SPLIT 表值函数返回一个空表。

例如，以下 SELECT 语句使用空格字符作为分隔符：

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

在实践运行中，前面的 SELECT 返回以下结果表：

value
`Lorem`
`ipsum`
`dolor`
`sit`
`amet.`

下面的示例通过为可选的第三个参数传递 ordinal 来启用 1 列：

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

value	序号
`Lorem`	1
`ipsum`	2
`dolor`	3
`sit`	4
`amet.`	5

A. 拆分逗号分隔值字符串

分析逗号分隔值列表，并返回所有非空标记：

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'

SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';

如果分隔符之间没有任何内容，STRING_SPLIT 将返回空字符串。

WHERE RTRIM(value) <> ''这条SQL语句的含义是筛选出满足条件的行，条件是对value进行右侧空格去除后不等于空字符串。

B. 拆分一列中的逗号分隔值字符串

Product表中的某一列为逗号分隔的标记列表，如以下示例所示：

ProductId	Name	Tags
`1`	`Full-Finger Gloves`	`clothing,road,touring,bike`
`2`	`LL Headset`	`bike`
`3`	`HL Mountain Frame`	`bike,mountain`

下面的查询转换每个标记列表，并将它们与原始行联接起来：

SELECT ProductId, Name, value
FROM Product
    CROSS APPLY STRING_SPLIT(Tags, ',');

--CROSS APPLY仅在sql server和oracle支持，其他数据库不支持

ProductId	Name	value
`1`	`Full-Finger Gloves`	`clothing`
`1`	`Full-Finger Gloves`	`road`
`1`	`Full-Finger Gloves`	`touring`
`1`	`Full-Finger Gloves`	`bike`
`2`	`LL Headset`	`bike`
`3`	`HL Mountain Frame`	`bike`
`3`	`HL Mountain Frame`	`mountain`

（输出的顺序可能变化，顺序不保证与输入字符串中的子字符串顺序匹配）

C. 按值聚合

用户必须创建一个报表，表中显示每个标记的产品数量并按产品数量排序，然后只筛选出产品数量在两个以上的标记。

SELECT value as tag, COUNT(*) AS [number_of_articles]
FROM Product
    CROSS APPLY STRING_SPLIT(Tags, ',')
GROUP BY value
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC;

D. 按标记值搜索

创建按关键字查找的查询，可以使用以下方式：

查找具有单个标记 (clothing) 的产品：

SELECT ProductId, Name, Tags
FROM Product
WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));

查找具有两个指定标记（clothing 和 road）的产品：

SELECT ProductId, Name, Tags
FROM Product
WHERE EXISTS (SELECT *
    FROM STRING_SPLIT(Tags, ',')
    WHERE value IN ('clothing', 'road'));

E. 按一系列值查找行

创建一个按 ID 列表查找的查询，可以使用以下语句：

SELECT ProductId, Name, Tags
FROM Product
JOIN STRING_SPLIT('1,2,3',',')
    ON value = ProductId;

上述 STRING_SPLIT 使用情况是常见反模式的替代。此类反模式可能涉及在应用程序层或 Transact-SQL 中创建动态 SQL 字符串。或者可以通过使用 LIKE 运算符来实现反模式。请参阅以下示例 SELECT 语句：（？）

SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

F. 按序号值查找行

以下语句将查找具有偶数索引值的所有行：

SELECT *
FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1)
WHERE ordinal % 2 = 0;

value	序号
Texas	2
Washington	4
Colorado	6

G. 按序号值为行排序

下面的语句返回输入字符串的拆分子字符串值及其序号值，按 ordinal 列排序：

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;

value	序号
A	5
B	4
C	3
D	2
E	1

2.create function方法

1.实现

Oracle split函数-CSDN博客

--创建TYPE
CREATE OR REPLACE TYPE TYPE_SPLIT AS TABLE OF VARCHAR2 (4000);
/

--创建split函数
CREATE OR REPLACE FUNCTION SPLIT(P_STRING VARCHAR2, P_SEP VARCHAR2 := ',')
  RETURN TYPE_SPLIT PIPELINED 
IS
  IDX  PLS_INTEGER;
  V_STRING VARCHAR2(4000) := P_STRING;
BEGIN
  LOOP
    IDX := INSTR(V_STRING, P_SEP);
    IF IDX > 0 THEN
      PIPE ROW(SUBSTR(V_STRING, 1, IDX - 1));
      V_STRING := SUBSTR(V_STRING, IDX + LENGTH(P_SEP));
    ELSE
      PIPE ROW(V_STRING);
      EXIT;
    END IF;
  END LOOP;
END;
/

--sql使用
select * from table(split('asd,fqe,grwg,ff'));

select * from table(split('qwf}asf}f}qq}','}'));

2.解析

CREATE OR REPLACE TYPE TYPE_SPLIT AS TABLE OF VARCHAR2 (4000);

创建一个名为TYPE_SPLIT的类型，它是一个包含VARCHAR2类型元素的表，最大长度为4000。

CREATE OR REPLACE FUNCTION SPLIT(P_STRING VARCHAR2, P_SEP VARCHAR2 := ',')

RETURN TYPE_SPLIT PIPELINED

创建一个名为SPLIT的函数，接受两个参数：P_STRING（要分割的字符串）和P_SEP（分隔符，默认值为逗号）。函数返回一个TYPE_SPLIT类型的结果，此为管道化的表。

IS

IDX PLS_INTEGER; V_STRING VARCHAR2(4000) := P_STRING;

在函数内部，定义一个名为IDX的整数变量和一个名为V_STRING的VARCHAR2类型变量，初始值为P_STRING。

BEGIN LOOP

IDX := INSTR(V_STRING, P_SEP);

使用LOOP循环遍历V_STRING中的每个字符。使用INSTR函数查找V_STRING中P_SEP的位置，并将结果赋值给IDX。

IF IDX > 0 THEN

PIPE ROW(SUBSTR(V_STRING, 1, IDX - 1));

V_STRING := SUBSTR(V_STRING, IDX + LENGTH(P_SEP));

ELSE

PIPE ROW(V_STRING);

EXIT;

END IF;

END LOOP;

END;

如果IDX大于0，说明找到了分隔符。将V_STRING从第一个字符到分隔符前一个字符之间的子字符串作为一行数据通过PIPE ROW输出，并更新V_STRING为分隔符后的一个字符开始的子字符串。

如果没有找到分隔符，说明已经到达字符串末尾。将V_STRING作为一行数据通过PIPE ROW输出，并退出循环。

结束循环，结束函数定义。

3.学习

1.oracle_pipelined关键字

1.介绍

在Oracle中，当在函数声明中使用pipelined关键字时，它表示该函数将返回一个集合，而不是一个标量值。

使用pipelined关键字的函数被称为“管道函数”（pipelined function）。这种函数可以返回一个集合类型（比如表类型、VARRAY类型等），而且返回结果可以被其他SQL查询直接使用，就好像它是一个表一样。

通常，一个管道函数在其内部使用一个或多个SQL查询来填充一个集合，然后通过PIPE ROW语句将集合中的每一行返回给调用者。调用者可以在SQL查询中直接引用这个函数，而不必把它的结果存储到一个中间表中。

要使用管道函数的话，需要先进行一下类型的创建。

2.优点

这种返回多行数据在需要大数据量处理的时候很有用，比如这一段SQL可能全部查询出来要一分钟，但是使用这个方法可能很快就有数据，虽然不是全部，但是有的时候不需要一次性获取全部数据的时候是一种好方法，且在复杂的方法下更好地去处理一些复杂的逻辑查询。

2.oracle_instr函数

Oracle的INSTR函数用于查找字符串中指定子字符串的位置。它返回子字符串在源字符串中第一次出现的位置。

INSTR(source_string, search_string)    //用法1

INSTR(source_string, search_string, start_position, occurrence)    //用法2

source_string是要搜索的源字符串。
search_string是要查找的子字符串。
start_position是开始搜索的位置，如果省略则从源字符串的开头开始搜索。
occurrence是要查找的匹配项的次数，如果省略则返回第一次出现的位置。

INSTR函数返回一个整数，表示子字符串在源字符串中的位置。如果找不到子字符串，则返回0。

SELECT INSTR('hello world', 'o') AS position FROM dual;
--这将返回5，因为'o'第一次出现在源字符串的第5个位置

3.oracle_is和as的区别

在Oracle中，AS和IS关键字都可以用来引入存储过程、函数、触发器等PL/SQL块的主体部分。它们的用法和作用是相似的，但通常AS用于SQL命令和匿名块，而IS用于PL/SQL块的定义。

1.AS关键字通常用于引入匿名块或者SQL命令，例如在创建视图或者存储过程时使用。

CREATE OR REPLACE VIEW my_view AS
SELECT column1, column2 
FROM my_table;

2.IS关键字通常用于引入存储过程、函数、触发器等PL/SQL块的主体部分，例如在定义函数或存储过程时使用。

CREATE OR REPLACE FUNCTION get_employee_name (emp_id IN NUMBER) 
    RETURN VARCHAR2 
IS 
    emp_name VARCHAR2(100); 
BEGIN -- 在这里编写获取员工姓名的逻辑 
    SELECT employee_name INTO emp_name 
    FROM employees 
    WHERE employee_id = emp_id;

    RETURN emp_name;
END;
/