?
Solved

Use of LEFT, RIGHT functions

Posted on 2012-09-21
5
Medium Priority
?
519 Views
Last Modified: 2012-09-21
Hi there,
I have a field named CPT_Code_List that contains values such as

12345
12345,5678
23789

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
0
Comment
Question by:wdelaney05
  • 3
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38422730
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?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38422756
SELECT lab_test_name,
          TRIM(REGEXP_SUBSTR(cpt_code_list, '[^,]+', 1, COLUMN_VALUE)) cpt_code_list
  FROM yourtable,
       TABLE(SELECT     COLLECT(LEVEL)
                   FROM DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT(cpt_code_list, '[^,]+'))
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38422760
use indexof or something similar


left(field, indexof(field,","))
right(indexof(field,","),field.length-indexof(field,","))
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38422763
as noted above,  left and right are not oracle commands

nor is indexof
0
 

Author Comment

by:wdelaney05
ID: 38422873
sdstuber,
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!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

864 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