This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

something like this

win(秋天的菠菜)

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)

(#3931941@0)
Last Updated: 2007-9-14
This post has been archived. It cannot be replied.

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 -brother-pooh(Pooh哥哥); 2007-9-14 {356} (#3931930@0)
-- 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 -win(秋天的菠菜); 2007-9-14 (#3931933@0)

something like this -win(秋天的菠菜); 2007-9-14 {567} (#3931941@0)
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上看到过。如果分隔符中有多个重复的空格，还得用规则表达式处理一下。 -newkid(newkid); 2007-9-15 (#3933338@0)
Thank you 秋菠 :) -brother-pooh(Pooh哥哥); 2007-9-20 (#3944898@0)
哪有学sql 的书或者网站啊，不是指sql语法。先谢了 -ineedjobnow(找工中); 2007-9-20 (#3945994@0)

@Florida

something like this

Replies, comments and Discussions:

More Topics