• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Split address with Delimeter

How do I use regex to get the same output as following query. Did try but get errors like invalid identifier

SELECT
TRIM (SUBSTR (LICENSEFILE.ADDRESS, 1, INSTR (LICENSEFILE.ADDRESS,'$',1,1)- 1))AS ADDRESS1,
TRIM (SUBSTR (LICENSEFILE.ADDRESS, INSTR (LICENSEFILE.ADDRESS,'$',1,1)+ 1, (INSTR (LICENSEFILE.ADDRESS,'$', 1,2) - 1)- (INSTR (LICENSEFILE.ADDRESS, '$',1,1))))AS ADDRESS2,
 TRIM (SUBSTR (LICENSEFILE.ADDRESS, INSTR (LICENSEFILE.ADDRESS,'$',1,2)+ 1, (INSTR (LICENSEFILE.ADDRESS,'$',1,3) - 1) - (INSTR (LICENSEFILE.ADDRESS, '$', 1, 2))))AS ADDRESS3,
TRIM (SUBSTR (LICENSEFILE.ADDRESS, INSTR (LICENSEFILE.ADDRESS, '$',1, 3) + 1)) AS ADDRESS4
FROM LICENSEFILE
0
GRChandrashekar
Asked:
GRChandrashekar
  • 5
  • 3
1 Solution
 
sdstuberCommented:
select regexp_substr(ADDRESS,'[^$]+',1,1),
regexp_substr(ADDRESS,'[^$]+',1,2),
regexp_substr(ADDRESS,'[^$]+',1,3),
regexp_substr(ADDRESS,'[^$]+',1,4)
from licensefile
0
 
sdstuberCommented:
oh,  I just saw you tagged 9i

regular expression support was introduced in 10g
0
 
GRChandrashekarAuthor Commented:
@sdstuber:

Oops ! is there any alternate to simplify or is this the only way out
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
you could write a function that will do the instr/substr for you


select delimited_element(ADDRESS,'[^$]+',1),
delimited_element(ADDRESS,'[^$]+',2),
delimited_element(ADDRESS,'[^$]+',3),
delimited_element(ADDRESS,'[^$]+',4)
from licensefile
FUNCTION delimited_element(p_str IN VARCHAR2, p_delim IN VARCHAR2, p_index IN INTEGER)
        RETURN VARCHAR2
        DETERMINISTIC
    IS
        v_start INTEGER;
        v_end   INTEGER;
    BEGIN
        IF p_index <= 0
        THEN
            -- selecting an invalid index, return null, no such element exists
            RETURN NULL;
        ELSIF p_index = 1
        THEN
            -- Index 1 is a special case, it can be undelimited and still be valid
            IF INSTR(p_str, p_delim) = 0
            THEN
                RETURN p_str;
            ELSE
                RETURN SUBSTR(p_str, 1, INSTR(p_str, p_delim) - 1);
            END IF;
        ELSE
            v_start := INSTR(p_str, p_delim, 1, p_index - 1);
            v_end := INSTR(p_str, p_delim, 1, p_index);

            IF v_start = 0 AND v_end = 0
            THEN
                -- selecting an invalid index, return null, no such element exists
                RETURN NULL;
            ELSIF v_end = 0
            THEN
                -- selecting the last element which doesn't require an end delimiter
                RETURN SUBSTR(p_str, v_start + 1);
            ELSE
                -- selecting something from the middle
                RETURN SUBSTR(p_str, v_start + 1, v_end - v_start - 1);
            END IF;
        END IF;
    END delimited_element;

Open in new window

0
 
GRChandrashekarAuthor Commented:
Seems to be good alternative. But,

select delimited_element(ADDRESS,'[^$]+',1), - GIVES OUTPUT BUT "$" DELIMETER REMAINS
delimited_element(ADDRESS,'[^$]+',2), - NO OUTPUT
delimited_element(ADDRESS,'[^$]+',3), - NO OUTPUT
delimited_element(ADDRESS,'[^$]+',4) - NO OUTPUT
0
 
sdstuberCommented:
oops sorry,  I just copied the previous example, you need to specify the delimiter, not an expression as the 2nd parameter


select delimited_element(ADDRESS,'$',1),
delimited_element(ADDRESS,'$',2),
delimited_element(ADDRESS,'$',3),
delimited_element(ADDRESS,'$',4)
from licensefile
0
 
GRChandrashekarAuthor Commented:
Gr8. Even if i migrate to 10g later on I think it works without any change.
Am i right?
0
 
sdstuberCommented:
yes, but in 10g and above regular expression parsing will be more efficient since it's a built in function
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now