使用 Sybase SQL Anywhere 拆分/分解逗号分隔的字符串 [英] Split/explode comma delimited string with Sybase SQL Anywhere

问题描述

<块引用>

更新:有人将此问题标记为重复如何拆分字符串以便我可以访问项目 x.但不同的是，我的问题是关于 Sybase SQL Anywhere，另一个是关于 MS SQL Server.这是两个不同的SQL引擎，即使同源，语法也不同.所以它不是重复的.我首先在描述和标签中写道，这一切都是关于 Sybase SQL Anywhere.

我有字段 id_list='1234,23,56,576,1231,567,122,87876,57553,1216'

我想用它来搜索IN这个字段:

SELECT *从表 1WHERE id IN (id_list)

• id 是 integer

• id_list 是 varchar/text

但是这样就行不通了，所以我需要以某种方式将 id_list 拆分为选择查询.

我应该在这里使用什么解决方案?我使用的是 T-SQL Sybase ASA 9 数据库 (SQL Anywhere).

在我看来，是通过 while 循环创建自己的函数，以及基于分隔符位置搜索分割的每个元素提取，然后将元素插入临时表，该函数将作为结果返回.

解决方案

正如 Mikael Eriksson 所说，dba.stackexchange.com 有两个非常好的解决方案，第一个使用 sa_split_list 系统程序，第二个使用 CAST 慢 语句.

对于 Sybase SQL Anywhere 9 sa_split_list 系统过程不存在，所以我做了 sa_split_list 系统过程替换(我使用了 bsivel 答案):

<块引用>

创建程序 str_split_list(在 str long varchar 中，在 delim char(10) 中默认为 ',')结果(line_num 整数，row_value long varchar)开始声明 str2 long varchar;声明位置整数；创建表#str_split_list (line_num 整数 DEFAULT AUTOINCREMENT,row_value long varchar null，主键(line_num))；SET str = TRIM(str) ||德利姆；SET 位置 = CHARINDEX(delim, str);分开行:当位置 >0 循环SET str2 = TRIM(LEFT(str, position - 1));插入 #str_split_list (row_value)值(str2)；SET str = RIGHT(str, LENGTH(str) - 位置);SET 位置 = CHARINDEX(delim, str);结束循环分离行；select * from #str_split_list order by line_num asc;结尾

以与sa_split_list相同的方式执行，使用默认分隔符,:

<块引用>

select * from str_split_list('1234,23,56,576,1231,567,122,87876,57553,1216')

或带有可以更改的指定分隔符:

<块引用>

select * from str_split_list('1234,23,56,576,1231,567,122,87876,57553,1216',',')

UPDATE: Someone marked this question as duplicate of How do I split a string so I can access item x. But it's different, my question is about Sybase SQL Anywhere, the other is about MS SQL Server. These are two different SQL engines, even if they have the same origin, they have different syntax. So it's not duplicate. I wrote in the first place in description and tags that it's all about Sybase SQL Anywhere.

I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216'

and I want to use it to search IN this field:

SELECT * 
FROM table1
WHERE id IN (id_list)

• id is integer

• id_list is varchar/text

But in this way this doesn't work, so I need in some way to split id_list into select query.

What solution should I use here? I'm using the T-SQL Sybase ASA 9 database (SQL Anywhere).

Way I see this, is to create own function with while loop through, and each element extract based on split by delimiter position search, then insert elements into temp table which function will return as result.

解决方案

Like Mikael Eriksson said, there is answer at dba.stackexchange.com with two very good solutions, first with use of sa_split_list system procedure, and second slower with CAST statement.

For the Sybase SQL Anywhere 9 sa_split_list system procedure not exist, so I have made sa_split_list system procedure replacement (I used parts of the code from bsivel answer):

CREATE PROCEDURE str_split_list
(in str long varchar, in delim char(10) default ',')
RESULT(
  line_num integer,
  row_value long varchar)
BEGIN
  DECLARE str2 long varchar;
  DECLARE position integer;

   CREATE TABLE #str_split_list (
   line_num integer DEFAULT AUTOINCREMENT,
   row_value long varchar null,
   primary key(line_num));

   SET str = TRIM(str) || delim;
   SET position = CHARINDEX(delim, str);

   separaterows:
   WHILE position > 0 loop
       SET str2 = TRIM(LEFT(str, position - 1));
       INSERT INTO #str_split_list (row_value)
       VALUES (str2);
       SET str = RIGHT(str, LENGTH(str) - position);
       SET position = CHARINDEX(delim, str);
    end loop separaterows;

   select * from #str_split_list order by line_num asc;

END

Execute the same way as sa_split_list with default delimiter ,:

select * from str_split_list('1234,23,56,576,1231,567,122,87876,57553,1216')

or with specified delimiter which can be changed:

select * from str_split_list('1234,23,56,576,1231,567,122,87876,57553,1216', ',')

这篇关于使用 Sybase SQL Anywhere 拆分/分解逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！