×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

something like this

SELECT DISTINCT token FROM (
SELECT TRIM (SUBSTR (txt,
INSTR (txt, ',', 1, LEVEL) + 1,
INSTR (txt, ',', 1, LEVEL + 1)
- INSTR (txt, ',', 1, LEVEL)
- 1
)
) AS token
FROM (SELECT ',' || '111,333,333,444,' || ',' txt
FROM DUAL)
CONNECT BY LEVEL <=
LENGTH ('111,333,333,444')
- LENGTH (REPLACE ('111,333,333,444', ',', ''))
+ 1)
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / Hello DXs, in Oracle, how to return only unique words (space is delimiter) from a column (which is CLOB data type)? If function is a good answer, how to achieve it? Thanks
    -- Note: The string could be several thousand characters in each record.

    Create Table Test_Clob (Id Number(15), Test_Field Clob);

    Insert Into Test_Clob (1,'This is a test test only test');
    Insert Into Test_Clob (1,'Test again and again');
    .
    .

    -- Expect result set
    ----------------------------
    1 This is a test only
    2 Test again and
    .
    .
    • one single SQL will do
      • something like this
        SELECT DISTINCT token FROM (
        SELECT TRIM (SUBSTR (txt,
        INSTR (txt, ',', 1, LEVEL) + 1,
        INSTR (txt, ',', 1, LEVEL + 1)
        - INSTR (txt, ',', 1, LEVEL)
        - 1
        )
        ) AS token
        FROM (SELECT ',' || '111,333,333,444,' || ',' txt
        FROM DUAL)
        CONNECT BY LEVEL <=
        LENGTH ('111,333,333,444')
        - LENGTH (REPLACE ('111,333,333,444', ',', ''))
        + 1)
        • 很巧妙的办法,我在ASKTOM上看到过。如果分隔符中有多个重复的空格,还得用规则表达式处理一下。
        • Thank you 秋菠 :)
        • 哪有学sql 的书或者网站啊,不是指sql语法。先谢了