please sole it [message #604079] |
Mon, 23 December 2013 22:43 |
|
One column in item
ITEM_CODE
919680010
919680010-1
919680010-1/95
919680010-1/90
919680010-1/75
919680013-4/20
419560297/10
419560298/15
i want to get out put in item code thes how can i pick this item code
ITEM CODE
919680010
919680010-1
919680013-4
419560297
419560298
|
|
|
|
|
Re: please sole it [message #604091 is a reply to message #604079] |
Mon, 23 December 2013 23:47 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
SQL> WITH DATA AS(
2 SELECT '919680010' ITEM_CODE FROM dual UNION ALL
3 SELECT '919680010-1' FROM dual UNION ALL
4 SELECT '919680010-1/95' FROM dual UNION ALL
5 SELECT '919680010-1/90' FROM dual UNION ALL
6 SELECT '919680010-1/75' FROM dual UNION ALL
7 SELECT '919680013-4/20' FROM dual UNION ALL
8 SELECT '419560297/10' FROM dual UNION ALL
9 SELECT '419560298/15' FROM dual)
10 SELECT CASE
11 WHEN REGEXP_LIKE(ITEM_CODE, '-') AND REGEXP_LIKE(ITEM_CODE, '/') THEN
12 SUBSTR(ITEM_CODE, 1, INSTR(ITEM_CODE, '/') - 1)
13 WHEN REGEXP_LIKE(ITEM_CODE, '/') THEN
14 SUBSTR(ITEM_CODE, 1, INSTR(ITEM_CODE, '/') - 1)
15 ELSE
16 ITEM_CODE
17 END ITEM_CODE
18 FROM DATA
19 GROUP BY CASE
20 WHEN REGEXP_LIKE(ITEM_CODE, '-') AND
21 REGEXP_LIKE(ITEM_CODE, '/') THEN
22 SUBSTR(ITEM_CODE, 1, INSTR(ITEM_CODE, '/') - 1)
23 WHEN REGEXP_LIKE(ITEM_CODE, '/') THEN
24 SUBSTR(ITEM_CODE, 1, INSTR(ITEM_CODE, '/') - 1)
25 ELSE
26 ITEM_CODE
27 END;
ITEM_CODE
--------------
919680010
419560297
919680010-1
919680013-4
419560298
|
|
|