OraFAQ Forum: SQL & PL/SQL » Separate string based on Comma (merged 2)

Home » SQL & PL/SQL » SQL & PL/SQL » Separate string based on Comma (merged 2) (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Separate string based on Comma (merged 2) [message #599726]

Mon, 28 October 2013 07:00

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Hi ALL,

This I want TO separate TO differnet COLUMNS based ON comma.
THE RULE IS LIKE out OF total five fields FIRST 3 comma will be FIRST 3 addresses AND rest will be address4 AND LAST NUMBER should appear IN pincode field IF found.
The trouble is for reading reverse to get the number.

WITH address AS (SELECT 'Avenue Supermarts Pvt Ltd,Anjaneya, Opp Hiranandani Foundation School, Powai, Mumbai,Pin Code 400076' addr1 FROM dual UNION ALL
                  SELECT 'Plot No. J-I, Block B-I, Mohan Co-operative Industrial Area, Mathura Road, New Delhi-110044' addr1 FROM dual UNION ALL
                  SELECT 'Padmashree Arcade, NH 5, Chinagantiyda Main Road, Gajuwaka, Vishakhapatnam' addr1 FROM dual UNION ALL
                  SELECT 'The Icon, 2nd  3rd Floor, #8, 80 Feet Road, HAL III Stage, Indiranagar, Banglore-560075' addr1 FROM dual UNION ALL
                  SELECT '13/1, International Airport Road, Bettahalasur Post, Bengaluru-562157' addr1 FROM dual)
SELECT addr1 FROM address;

Report message to a moderator

Re: Separate string based on Comma (merged 2) [message #599734 is a reply to message #599726]

Mon, 28 October 2013 07:28

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What should be the result for the data you gave?

Report message to a moderator

Re: Separate string based on Comma (merged 2) [message #599740 is a reply to message #599726]

Mon, 28 October 2013 08:05

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Not thoroughly tested, but, you should be able to move further with this, a bit old method, though this is what I have in mind now -

SQL> WITH address AS (SELECT 'Avenue Supermarts Pvt Ltd,Anjaneya, Opp Hiranandani Foundation School, Powai, Mumbai,Pin Code 400076' addr1 FROM dual UNION ALL
  2                    SELECT 'Plot No. J-I, Block B-I, Mohan Co-operative Industrial Area, Mathura Road, New Delhi-110044' addr1 FROM dual UNION ALL
  3                    SELECT 'Padmashree Arcade, NH 5, Chinagantiyda Main Road, Gajuwaka, Vishakhapatnam' addr1 FROM dual UNION ALL
  4                    SELECT 'The Icon, 2nd  3rd Floor, #8, 80 Feet Road, HAL III Stage, Indiranagar, Banglore-560075' addr1 FROM dual UNION ALL
  5                    SELECT '13/1, International Airport Road, Bettahalasur Post, Bengaluru-562157' addr1 FROM dual)
  6  SELECT SUBSTR(ADDR1, 1, INSTR(ADDR1, ',', 1, 1) - 1) ADDR_1,
  7         SUBSTR(ADDR1,
  8                INSTR(ADDR1, ',', 1, 1) + 1,
  9                INSTR(ADDR1, ',', 1, 2) - INSTR(ADDR1, ',', 1, 1) - 1) ADDR_2,
 10         SUBSTR(ADDR1,
 11                INSTR(ADDR1, ',', 1, 2) + 1,
 12                INSTR(ADDR1, ',', 1, 3) - INSTR(ADDR1, ',', 1, 2) - 1) ADDR_3,
 13         SUBSTR(ADDR1,
 14                INSTR(ADDR1, ',', 1, 3) + 1,
 15                INSTR(ADDR1, ',', 1, 4) - INSTR(ADDR1, ',', 1, 3) - 1) ADDR_4,
 16         SUBSTR(ADDR1, INSTR(ADDR1, ',', 1, 4) + 1, 100) ADDR_4,
 17         REGEXP_REPLACE(SUBSTR(ADDR1, INSTR(ADDR1, ',', 1, 3) + 1, 100),
 18                        '[^[:digit:]]') PIN_CODE
 19    FROM ADDRESS;
 
ADDR_1                     ADDR_2                       ADDR_3                             ADDR_4       ADDR_5                                                                PIN_CODE

Avenue Supermarts Pvt Ltd  Anjaneya                     Opp Hiranandani Foundation School  Powai        Mumbai,Pin Code 400076                                                400076
Plot No. J-I               Block B-I                    Mohan Co-operative Industrial Area Mathura Road New Delhi-110044                                                      110044
Padmashree Arcade          NH 5                         Chinagantiyda Main Road            Gajuwaka     Vishakhapatnam                                                         
The Icon                   2nd  3rd Floor               #8                                 80 Feet Road HAL III Stage, Indiranagar, Banglore-560075                           80560075
13/1                       International Airport Road   Bettahalasur Post                               13/1, International Airport Road, Bettahalasur Post, Bengaluru-562157 562157

Report message to a moderator

Re: Separate string based on Comma (merged 2) [message #599744 is a reply to message #599734]

Mon, 28 October 2013 09:08

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

first comma		 second comma	                    third comma           rest till comma or hyphen found    number from reverse end       
addr1	          	           addr2                          addr3	                               addr4	                          pincode
Avenue Supermarts Pvt Ltd	Anjaneya	Opp Hiranandani Foundation School	                 Powai,  Mumbai         	400076
Plot No. J-I		 	Block B-I 	 Mohan Co-operative Industrial Area                     Mathura Road,  New Delhi          110044
Padmashree Arcade	         NH 5	 	     Chinagantiyda Main Road                         Gajuwaka, Vishakhapatnam
The Icon	             2nd  3rd Floor	 	   #8                        80 Feet Road,HAL III Stage, Indiranagar, Banglore    560075
13/1	                   International Airport Road	 Bettahalasur Post	                     Bengaluru	                         562157

Report message to a moderator

Re: Separate string based on Comma (merged 2) [message #599746 is a reply to message #599744]

Mon, 28 October 2013 09:35

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

That is exactly matching what I required. But is there any way by which we can read a string from reverse end.

Report message to a moderator

Re: Separate string based on Comma (merged 2) [message #599747 is a reply to message #599746]

Mon, 28 October 2013 09:45

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

sss111ind wrote on Mon, 28 October 2013 20:05

That is exactly matching what I required.

Are you quoting to my post?

Quote:

But is there any way by which we can read a string from reverse end.

Of course you could do that„ the start position needs to be negative in the SUBSTR. You cannot do that using REGULAR EXPRESSION. Let me tweak my code, will post in sometime.

Report message to a moderator

Re: Separate string based on Comma (merged 2) [message #599748 is a reply to message #599747]

Mon, 28 October 2013 09:54

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Yes actually I always replying with quick reply option so it is difficult to trace for which post I replied.Thanks

Report message to a moderator

Re: Separate string based on Comma (merged 2) [message #599750 is a reply to message #599748]

Mon, 28 October 2013 10:31

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

The way you want for PIN_CODES. Since these are Indian pin codes which are always 6 digits, so just taking 6 digits from the end of string in reverse. The translate and replace is used to skip the cases where there are no pin codes. Regular expression would also and might look shorter in code, however, the reverse positioning is not possible with REGEXP.

WITH address AS (SELECT 'Avenue Supermarts Pvt Ltd,Anjaneya, Opp Hiranandani Foundation School, Powai, Mumbai,Pin Code 400076' addr1 FROM dual UNION ALL
                      SELECT 'Plot No. J-I, Block B-I, Mohan Co-operative Industrial Area, Mathura Road, New Delhi-110044' addr1 FROM dual UNION ALL
                      SELECT 'Padmashree Arcade, NH 5, Chinagantiyda Main Road, Gajuwaka, Vishakhapatnam' addr1 FROM dual UNION ALL
                      SELECT 'The Icon, 2nd  3rd Floor, #8, 80 Feet Road, HAL III Stage, Indiranagar, Banglore-560075' addr1 FROM dual UNION ALL
                      SELECT '13/1, International Airport Road, Bettahalasur Post, Bengaluru-562157' addr1 FROM dual)
SELECT Substr(addr1, 1, Instr(addr1, ',', 1, 1) - 1) 
       ADDR_1, 
       Substr(addr1, Instr(addr1, ',', 1, 1) + 1, Instr(addr1, ',', 1, 2) - 
                                                  Instr(addr1, ',', 1, 1) - 1) 
       ADDR_2, 
       Substr(addr1, Instr(addr1, ',', 1, 2) + 1, Instr(addr1, ',', 1, 3) - 
                                                  Instr(addr1, ',', 1, 2) - 1) 
       ADDR_3, 
       Substr(addr1, Instr(addr1, ',', 1, 3) + 1, Instr(addr1, ',', 1, 4) - 
                                                  Instr(addr1, ',', 1, 3) - 1) 
       ADDR_4, 
       Substr(addr1, Instr(addr1, ',', 1, 4) + 1, 100) 
       ADDR_4, 
       CASE 
         WHEN Replace(Translate(Substr(addr1, -6), 0123456789, 0), 0, '') IS NOT 
              NULL 
       THEN NULL 
         ELSE Substr(addr1, -6) 
       END 
       PIN_CODE
FROM   address;

This will give the same output as you stated.

Regards,
Lalit

Report message to a moderator

Re: Separate string based on Comma (merged 2) [message #599790 is a reply to message #599750]

Tue, 29 October 2013 01:00

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Thank you very much All .

Report message to a moderator

Re: Separate string based on Comma (merged 2) [message #599793 is a reply to message #599790]

Tue, 29 October 2013 01:13

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Well I was interested to know is my test case working for you?
Is it generic enough to handle all the rows?
Or, did you tweak my code?
Did you understand the logic for getting the pin codes?

A feedback would be appreciated.

Regards,
Lalit

Report message to a moderator

Re: Separate string based on Comma (merged 2) [message #599813 is a reply to message #599793]

Tue, 29 October 2013 03:58

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Yes It's worked perfectly.
It's sufficient for my requirement.
No I have not done any modification.
Yes reading from revers end for first 6 character if found character not to display else display.

Thank you so much.

[Updated on: Tue, 29 October 2013 03:59]

Report message to a moderator

Previous Topic:	why it doesnt save in table
Next Topic:	Concatenating address fields

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 12:16:01 CDT 2024