Link to home
Create AccountLog in
Avatar of Swadhin Ray
Swadhin RayFlag for United States of America

asked on

split one string into multiple values

I have a string like below:

'170 W Tasman Dr~~~~San Jose~CA~95134~US'

Now I want to split into multiple columns and '~' symbol is should be the separator.
 So the result should look like :

col1      col2      col3      col4      col5      col6      col7      col8      col9
170 W Tasman Dr                              San Jose      CA      95134      US

Sample code:
SELECT REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+') COL1,
        REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,2) COL2,
            REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,3) COL3,
            REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,4) COL4,
            REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,5) COL5,
    REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,6) COL6,
    REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,7) COL7,
    REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,8) COL8,
    REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,9) COL9
FROM dual;
Avatar of Pratima
Pratima
Flag of India image

see this


SELECT REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^ ]+') COL1,
        REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^ ]+',1,2) COL2,
            REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^ ]+',1,3) COL3,
            REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^ ~]+',1,4) COL4,
            REGEXP_SUBSTR (REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,2) ,'[^ ]+',1,1) COL5,
    REGEXP_SUBSTR (REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,2) ,'[^ ]+',1,2) COL6,
    REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,3) COL7,
    REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,4) COL8,
    REGEXP_SUBSTR('170 W Tasman Dr~~~~San Jose~CA~95134~US','[^~]+',1,5) COL9
FROM dual;
SOLUTION
Avatar of Anuradha Goli
Anuradha Goli
Flag of Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Swadhin Ray

ASKER

the symbol '~' is the separator, so my first column will have :
'170 W Tasman Dr'
and next column should not have anything as '~~'
So when the symbol do not have any thing between them but still null values should show like for col2 , col3 and col4 and then again the values ...
@anuradhay : Thanks for your comment but I am looking for a solution in oracle not with SQL Server.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks