Use of LEFT, RIGHT functions

Posted on 2012-09-21
Last Modified: 2012-09-21
Hi there,
I have a field named CPT_Code_List that contains values such as


sample data for lab tests and their CPT codes will look like this:
Lab_Test_Name                        CPT_Code_List
Lab Test A                                 12345
Lab Test B                                12345, 5678
Lab Test C                                  23789

The CPT_Code_List contains codes for certain lab tests. Some lab tests can have multiple CPT codes, hence the multiple values in the field sometimes. Is it possible, with the use of a RIGHT or LEFT function to get single values broken out from the CPT_Code_List
field? So that I can get data results that look like this:
Lab_Test_Name                        CPT_Code_List_break_out
Lab Test A                                 12345
Lab Test B                                12345
Lab Test B                                5678
Lab Test C                                  23789
Question by:wdelaney05
    LVL 73

    Expert Comment

    RIGHT and LEFT are sql server commands, not Oracle.

    however you can still get your results.

    will the list always have 1 or 2 values or could it have many?
    LVL 73

    Accepted Solution

    SELECT lab_test_name,
              TRIM(REGEXP_SUBSTR(cpt_code_list, '[^,]+', 1, COLUMN_VALUE)) cpt_code_list
      FROM yourtable,
                       FROM DUAL
                 CONNECT BY LEVEL <= REGEXP_COUNT(cpt_code_list, '[^,]+'))
    LVL 15

    Expert Comment

    by:Ess Kay
    use indexof or something similar

    left(field, indexof(field,","))
    LVL 73

    Expert Comment

    as noted above,  left and right are not oracle commands

    nor is indexof

    Author Comment

    thanks for your input! I ran the indexof code from esskayb2d and then I realized that it is not an Oracle function.
    Sometimes there can be more than 2 values in CPT_code_List. Your TRIM(REGEXP_SUBSTR..)  works great!

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now