Home » SQL & PL/SQL » SQL & PL/SQL » how to remove more than one value (Oracle10g)
| how to remove more than one value [message #645723] |
Mon, 14 December 2015 04:16  |
 |
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
|
Member |
|
|
hi all,
I want to remove the multiple values... values may be randaom .So when i try this query it gives the output.But when i give last value it is not giving me the correct output.
SELECT pages , REGEXP_REPLACE (pages,'6088,10733,8909,10745,10731')
FROM test_n
WHERE affiliate_id =1677900296;
but when i give last value it is not remove from list... please help
my dat
1677900296.00 6088,8326,8909,9751,10731,10733,10735,10737,10739,10741,10743,10745,10747,10749,11471 qatestuser2 11/12/2013 4:50:07 AM Glam 20131112 UK
1681789196.00 6088,6100,6128,6366,6509,6514,6520,6528,6533,6567,6590,8266,8326,8346,8366,8909,10075,10391,10393,10395,10397,10731,10733,10735,10737 ,10739,10741,10743,10745,10747,10749,11471,12211 janett 1/12/2014 2:45:25 PM Glam 20140112 janett 1/12/0014 20140112 US
|
|
|
|
|
|
| Re: how to remove more than one value [message #645726 is a reply to message #645724] |
Mon, 14 December 2015 04:48  |
|
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
|
Member |
|
|
SELECT REGEXP_REPLACE ('6088,8326,8909,9751,10731,10733,10735,10737,10739,10741,10743,10745,10747,10749,11471','6088,10733,8909,10745,10731')
FROM dual
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to remove more than one value [message #645741 is a reply to message #645740] |
Mon, 14 December 2015 06:16 |
|
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
|
Member |
|
|
from this query i want to remove starting comma and ending comma, please help
SELECT REGEXP_REPLACE ('6088,8326,8909,9751,10731,10733,10735,10737,10739,10741,10743,10745,10747,10749,11471','6088|10733|8909|10745|11471')
,replace(REGEXP_REPLACE ('6088,8326,8909,9751,10731,10733,10735,10737,10739,10741,10743,10745,10747,10749,11471','6088|10733|8909|10745|11471'),',,',',')
FROM dual;
|
|
|
|
|
|
|
|
| Re: how to remove more than one value [message #645762 is a reply to message #645752] |
Mon, 14 December 2015 13:38 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
Just curious. Why do you have such a horrible layout for your data. If you had a table with the first column the affiliate_id and the second column the page number, Your requirement would be very easy to do and would be much easier to maintain and display. Storing multiple data in a single column is almost never a good idea.
|
|
|
|
| Re: how to remove more than one value [message #645765 is a reply to message #645752] |
Mon, 14 December 2015 15:01 |
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
JNagtzaam wrote on Mon, 14 December 2015 10:04TRIM(',' from '<list>')
Well, TRIM(BOTH ',' from '<list>'). But that's least of the issues. Pattern '6088|10733|8909|10745|11471' replaces sequence of digits not list elements. For example if list has element 123608845 this pattern will change it to 12345 while it should stay intact. First we need to duplicate commas, then use '(^|,)(6088|10733|8909|10745|11471)(,|$)', replace multiple commas with single comma and only then use trim. Another solution would be split list into elements and combine them again excluding ones we don't need.
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jun 25 00:49:30 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
