asked on

Use of LEFT, RIGHT functions

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

Sean Stuber

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?

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

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

Start Free Trial

Ess Kay

use indexof or something similar

left(field, indexof(field,","))
right(indexof(field,","),field.length-indexof(field,","))

Sean Stuber

as noted above, left and right are not oracle commands

nor is indexof

W D

ASKER

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!