asked on

REGEXP in SQL

SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA', ',[^,]+,') RESULT
FROM dual;

output: , Redwood Shores,
how can i extract
1.., CA,
2.., Redwood Shores, CA
3..Redwood Shores
4..500 Oracle Parkway,
5..USA
Thanks in advance.

sathyagiri

You could use something like
select substr('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,2),
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,3) -
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,2)+1) from dual

,CA,

select substr('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,1),
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,3) -
INSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA',',',1,1)) from dual

Otherwise use a combination of regexp_substr and regexp_instr

avi_ny

ASKER

Hi
Thanks,
I can do it with INSTR and SUBSTR but i want do it with REGEXP_SUBSTR.
Please can you tell me how

sathyagiri

Try this
select REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA', ',[^,]+,'1,2) RESULT
FROM dual;
select REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA', ',[^,]+,'1,1) RESULT
FROM dual;

avi_ny

ASKER

Hi sathyagiri,
Thanks.
But why select REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA, 08820 , USA', ',[^,]+,'1,2) RESULT
FROM dual;
does not retuen ,CA, which is after second occurence of ,. why it returns ,08820,
Can you explain it please.

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial