Home » SQL & PL/SQL » SQL & PL/SQL » Regular Expressions (Oracle)
Regular Expressions [message #664213] Fri, 07 July 2017 02:33 Go to next message
oomjai2233
Messages: 6
Registered: July 2017
Junior Member
Hi Team,

I need to replace the same character(remove Duplicate char) from a column.

Source:
empid region
1 C
2 L,L,L,L
3 L,E,E,L,P
4 A,A,S,D
5 A,A

So i want output like below

empid region
1 C
2 L
3 L,E,P
4 A,S,D
5 A
Re: Regular Expressions [message #664214 is a reply to message #664213] Fri, 07 July 2017 02:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

What SQL have you tried so far?
Re: Regular Expressions [message #664215 is a reply to message #664213] Fri, 07 July 2017 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

There are many topics on the subject here, for instance this one.

Re: Regular Expressions [message #664216 is a reply to message #664214] Fri, 07 July 2017 03:01 Go to previous messageGo to next message
oomjai2233
Messages: 6
Registered: July 2017
Junior Member
Hi,

Tried with ([^,]+)(,[ ]*\1)+ but not removing the duplicates

Thanks
Re: Regular Expressions [message #664217 is a reply to message #664216] Fri, 07 July 2017 03:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Come on, man. Am I supposed to construct the example for you? You have not even shown the SQL statement.
Re: Regular Expressions [message #664218 is a reply to message #664217] Fri, 07 July 2017 03:13 Go to previous messageGo to next message
oomjai2233
Messages: 6
Registered: July 2017
Junior Member
Hi,

Really sorry

SELECT EMPID,REGEXP_REPLACE(REGION|| ',',' ([^,]+)(,[ ]*\1)+ ,'\1') FROM REGION_TEST ;
Re: Regular Expressions [message #664219 is a reply to message #664218] Fri, 07 July 2017 03:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
And the CREATE TABLE REGION_TEST statement? With the five INSERT statements?

PErhaps someone will help if you do that - not me, I'm going to a client site now.
Re: Regular Expressions [message #664220 is a reply to message #664218] Fri, 07 July 2017 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you read the link I gave you?
And also those we gave you about formatting you code?
And also what is your Oracle version (again)?

Re: Regular Expressions [message #664221 is a reply to message #664219] Fri, 07 July 2017 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

John Watson wrote on Fri, 07 July 2017 10:14
...
PErhaps someone will help if you do that - not me, I'm going to a client site now.

I will if oomjai2233 follows the rules, posts what is asked, answers the questions, and shows us what he tried from the topic I pointed him/her to. Smile

[Updated on: Fri, 07 July 2017 03:18]

Report message to a moderator

Re: Regular Expressions [message #664224 is a reply to message #664221] Fri, 07 July 2017 04:30 Go to previous messageGo to next message
oomjai2233
Messages: 6
Registered: July 2017
Junior Member
sure my code is here
create table qa_poc_hp.REGION(EMPID INTEGER,REGION VARCHAR(20))

INSERT INTO QA_POC_HP.REGION VALUES(1,'C');
INSERT INTO QA_POC_HP.REGION VALUES(2,'L,L,L,L');
INSERT INTO QA_POC_HP.REGION VALUES(3,'L,E,E,L,P');
INSERT INTO QA_POC_HP.REGION VALUES(4,'A,A,S,D');
INSERT INTO QA_POC_HP.REGION VALUES(5,'A,A');

sample records

EMPID	REGION
1	C
2	L,L,L,L
3	L,E,E,L,P
4	A,A,S,D
5	A,A


the query i m running
SELECT EMPID,REGEXP_REPLACE(REGION|| ',',' ([^,]+)(,[ ]*\1)+ ,'\1') FROM REGION ;
The version is ,Oracle 11g

Re: Regular Expressions [message #664225 is a reply to message #664224] Fri, 07 July 2017 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, now

Michel Cadot wrote on Fri, 07 July 2017 10:15

Did you read the link I gave you?
...
Re: Regular Expressions [message #664226 is a reply to message #664225] Fri, 07 July 2017 05:18 Go to previous messageGo to next message
oomjai2233
Messages: 6
Registered: July 2017
Junior Member
Hi,i have seen the code but when i am applying that in to my code it is behaving something different.i am more interested in single line function(regexp_replace).

Mysql
SELECT empid,REGEXP_REPLACE(region,'(^|\-)([^-]+\-)(.*\-)?\2','\1\2\3') FROM REGION

my output not changed
empid   REGEXP_REPLACE
5              A,A
4              A,A,S,D
3              L,E,E,L,P
1              C
2              L,L,L,L

Re: Regular Expressions [message #664227 is a reply to message #664226] Fri, 07 July 2017 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This solution does not work, it has been demonstrated in the topic.
Why don't you try those that work?

Re: Regular Expressions [message #664228 is a reply to message #664227] Fri, 07 July 2017 05:52 Go to previous messageGo to next message
oomjai2233
Messages: 6
Registered: July 2017
Junior Member
i have multiple columns and need to delete the duplicates , so the other way which was mentioned was the WITH clause .
is it possible with REgular expression
Re: Regular Expressions [message #664229 is a reply to message #664228] Fri, 07 July 2017 05:56 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read the first answer of the other topic.

Previous Topic: Functional based index
Next Topic: Get last 200 records from table in oracle
Goto Forum:
  


Current Time: Thu Mar 28 16:10:24 CDT 2024