我可以通过给出 regexp_substr 的查询来创建选择组吗？

发布于 2024-10-14 06:23:30 字数 329 浏览 8 评论 0原文

我有一个名为 data_column 的列，它有一些值，例如“123123,12,123123”。我想计算按第二个分组的行数。

但是当我运行时

select count(*) from table group by regexp_substr(data_column,'[^,]+',1,2);

它给出了

ORA-00932: incostintent datatypes: Expected: - got: CLOB 00932. 00000 - “数据类型不一致：预期 %s 得到 %s”

我不能按正则表达式子字符串分组吗？

原文

I have a column named data_column, it has some value like "123123,12,123123". I want to count rows grouped by the second one.

But when i run

select count(*) from table group by regexp_substr(data_column,'[^,]+',1,2);

It gives

ORA-00932: incostintent datatypes: expected: - got: CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"

Cant i group by a regex substring?

分享到QQ

分享到微博

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

发布评论

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

同尘 2024-10-21 06:23:30

问题不是来自 regexp_substr 函数，而是来自您的列数据类型：

SQL> CREATE TABLE t (data_column CLOB);

Table created
SQL> INSERT INTO t VALUES ('123123,12,123123');

1 row inserted
SQL> INSERT INTO t VALUES ('123124,12,123123');

1 row inserted
SQL> INSERT INTO t VALUES ('123125,11,123123');

1 row inserted

SQL> SELECT regexp_substr(data_column,'[^,]+',1,2) FROM t;

REGEXP_SUBSTR(DATA_COLUMN,'[^,
--------------------------------------------------------------------------------
12
12
11

在这里您可以看到该函数的行为正确，但是 Oracle（使用 10.2 进行测试）不允许您使用 clob 进行分组列：

SQL> select count(*) from t group by data_column;

select count(*) from t group by data_column

ORA-00932: inconsistent datatypes: expected - got CLOB

您可以将函数输出转换为 VARCHAR2 以执行 GROUP BY：

SQL> SELECT dbms_lob.substr(regexp_substr(data_column,'[^,]+',1,2), 4000),
  2         COUNT(*)
  3   FROM t
  4  GROUP BY dbms_lob.substr(regexp_substr(data_column,'[^,]+',1,2), 4000);

DBMS_LOB.SUBSTR(REGEXP_SUBSTR(    COUNT(*)
------------------------------- ----------
12                                       2
11                                       1

the problem doesn't come from the regexp_substr function but from your column data type:

SQL> CREATE TABLE t (data_column CLOB);

Table created
SQL> INSERT INTO t VALUES ('123123,12,123123');

1 row inserted
SQL> INSERT INTO t VALUES ('123124,12,123123');

1 row inserted
SQL> INSERT INTO t VALUES ('123125,11,123123');

1 row inserted

SQL> SELECT regexp_substr(data_column,'[^,]+',1,2) FROM t;

REGEXP_SUBSTR(DATA_COLUMN,'[^,
--------------------------------------------------------------------------------
12
12
11

Here you see that the function behaves correctly, however Oracle (tested with 10.2) doesn't allow you to group with a clob column:

SQL> select count(*) from t group by data_column;

select count(*) from t group by data_column

ORA-00932: inconsistent datatypes: expected - got CLOB

You can convert the function output to a VARCHAR2 to perform the GROUP BY:

SQL> SELECT dbms_lob.substr(regexp_substr(data_column,'[^,]+',1,2), 4000),
  2         COUNT(*)
  3   FROM t
  4  GROUP BY dbms_lob.substr(regexp_substr(data_column,'[^,]+',1,2), 4000);

DBMS_LOB.SUBSTR(REGEXP_SUBSTR(    COUNT(*)
------------------------------- ----------
12                                       2
11                                       1

回复收藏 0 原文

~没有更多了~