如何在甲骨文中分开的两个字符串之间做事

发布于 2025-01-30 11:52:18 字数 155 浏览 3 评论 0 原文

我该如何获取所有值的列表

我在数据库中具有存储为1234,5678的字符串中的值，如果我确实从room_number这样的学生中选择 * * 1234,5678'的学生，

；它只给出一个空的值，这两个数字如何使所有属于“ 1234”'5678'和'1234,5678'

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

还在原地等你 2025-02-06 11:52:18

您描述的方式，必须将逗号分隔的值分为行。

我不确定您的意思是说某人的房间编号真的可以是 1234,5678 ，但是 - 您可能会这样做；这就是联合所有查询的一部分。

select *
from students s
where s.room_number in (-- split string into rows, i.e. separate '1234,5678' to
                        -- '1234' and '5678'
                        select regexp_substr(t.string, '[^,]+', 1, column_value)
                        from other_table t cross join
                          table(cast(multiset(select level from dual
                                              connect by level <= regexp_count(t.string, ',') + 1
                                             ) as sys.odcinumberlist))
                        union all
                        -- this is a value you have in that other table "as is" 
                        -- (i.e. '1234,5678')
                        select t.string
                        from other_table t                                             
                       );

The way you described it, you'll have to split comma-separated values into rows.

I'm not sure what you mean by saying that someone's room number can really be 1234,5678 but hey - you probably do; that's what the UNION ALL part of the query does.

select *
from students s
where s.room_number in (-- split string into rows, i.e. separate '1234,5678' to
                        -- '1234' and '5678'
                        select regexp_substr(t.string, '[^,]+', 1, column_value)
                        from other_table t cross join
                          table(cast(multiset(select level from dual
                                              connect by level <= regexp_count(t.string, ',') + 1
                                             ) as sys.odcinumberlist))
                        union all
                        -- this is a value you have in that other table "as is" 
                        -- (i.e. '1234,5678')
                        select t.string
                        from other_table t                                             
                       );

回复收藏 0 原文

云淡风轻 2025-02-06 11:52:18

您无需将列表分为单独的条款。

您可以使用喜欢，并包围列表和匹配值与列表定界符：

SELECT *
FROM   students
WHERE  ',' || :yourList || ',' LIKE '%,' || room_number || ',%';

对于（硬编码的）值，它将是：

SELECT *
FROM   students
WHERE  ',1234,5678,' LIKE '%,' || room_number || ',%';

如果您将存储在另一个表中的值，则可以基于喜欢的加入：

SELECT s.*
FROM   students s
       INNER JOIN other_table o
       ON (',' || o.room_list || ',' LIKE '%,' || s.room_number || ',%');

对于示例数据：

CREATE TABLE students (id, room_number) AS
SELECT 1, '1234' FROM DUAL UNION ALL
SELECT 2, '5678' FROM DUAL UNION ALL
SELECT 3, '1234,5678' FROM DUAL UNION ALL
SELECT 4, '9999' FROM DUAL;

CREATE TABLE other_table (room_list) AS
SELECT '1234,5678' FROM DUAL;

输出：

id room_number

1 1234

2 5678

3 1234,5678

id	room_number
1	1234
2	5678
3	1234,5678

db＆lt;这里

You do not need to split the list into separate terms.

You can use LIKE and surround the list and the matching values with the list delimiter:

SELECT *
FROM   students
WHERE  ',' || :yourList || ',' LIKE '%,' || room_number || ',%';

Which for your (hard-coded) values would be:

SELECT *
FROM   students
WHERE  ',1234,5678,' LIKE '%,' || room_number || ',%';

If you have the values stored in another table then you can JOIN based on LIKE:

SELECT s.*
FROM   students s
       INNER JOIN other_table o
       ON (',' || o.room_list || ',' LIKE '%,' || s.room_number || ',%');

Which, for the sample data:

CREATE TABLE students (id, room_number) AS
SELECT 1, '1234' FROM DUAL UNION ALL
SELECT 2, '5678' FROM DUAL UNION ALL
SELECT 3, '1234,5678' FROM DUAL UNION ALL
SELECT 4, '9999' FROM DUAL;

CREATE TABLE other_table (room_list) AS
SELECT '1234,5678' FROM DUAL;

Outputs: