1

I'm extracting possible values from a description field in Oracle. There are thousands of them and I thought of doing it using SQL.

The issue is possible values are embedded in the description after the word "Acceptable values:" OR "Acceptable value:"

Also, the values have code and description, for ex.

00=No code

01=Additional code

I need the possible values extracted.

This is in Oracle table.

This is the source table:

Table1

VAR_NAME    VAR_DESC
Test 1      Code identifying test 1
            Acceptable values:
            00=No code
            01=Additional Code

Test 2      Code identifying test 2
            Acceptable value:
            44=No code

This is the output table:

VAR_NAME    VAR_DESC                    CODE        DESCRIPTION
Test 1      Code identifying test 1     00          No code
            Acceptable values:
            00=No code
            01=Additional Code

Test 1      Code identifying test 1     01          Additional Code
            Acceptable values:
            00=No code
            01=Additional Code


Test 2      Code identifying test 2     44          No code
            Acceptable value:
            44=No code

Is there a way in Oracle to extract this kind of information?

Improve this question

2 Answers 2

Reset to default
1

You can use regexp_count and regexp_instr together. The critical characters are = and carriage return (chr(10)) as in the following :

with table1(var_name,var_desc) as
(
 select 'Test 1',
        'Code identifying test 1
         Acceptable values:
         00=No code
         01=Additional Code' from dual union all
 select 'Test 2',
        'Code identifying test 2
         Acceptable value:
         44=No code'         from dual union all
 select 'Test 3',
        'Code identifying test 3' 
                             from dual       
), table2(var_name,str1,description) as
(
 select var_name,
        case when regexp_count(var_desc,chr(10)) > 0 then
             regexp_substr(var_desc,'[^=]+',regexp_instr(var_desc,chr(10), 1, level))
        end,
        case when regexp_count(var_desc,chr(10)) > 0 then
             regexp_substr(var_desc,'=(.*)+',regexp_instr(var_desc,chr(10), 1, level))
        end 
   from table1
connect by level <= regexp_count(var_desc,chr(10)) 
    and prior var_name = var_name
    and prior sys_guid() is not null   
)    
select t1.*, 
      decode(regexp_count(str1,chr(10)),1,str1) as code, ltrim(str1,'=') as description
 from table2 t2
 join table1 t1 on t1.var_name = t2.var_name
where decode(regexp_count(str1,chr(10)),1,str1) is not null
order by t1.var_name, code;

VAR_NAME    VAR_DESC                 CODE   DESCRIPTION
--------    -----------------------  ----   -----------
Test 1      Code identifying test 1  00     No code
            Acceptable values:
            00=No code
            01=Additional Code

Test 1      Code identifying test 1  01     Additional Code
            Acceptable values:
            00=No code
            01=Additional Code

Test 2      Code identifying test 2  44     No code
            Acceptable value:
            44=No code

Demo

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I'm getting the following error: ORA-01428: argument '0' is out of range 01428. 00000 - "argument '%s' is out of range" *Cause: *Action:
@dang Hi, Dhananjay. Actually, I shared the demo, and works fine. Didn't you try?
Yes. The demo works. However, when I run on my table, it gives me this error. Not sure why.
@dang try to issue SQL>alter session set "_disable_function_based_index" = TRUE; before calling the query.
@dang during this time interval, I was trying, and explored that yes you have some single line records for var_desc column, and issue stems from those columns. So, I added the constrint case when regexp_count(var_desc,chr(10)) > 0 then...
|
0

One of doing this is using the string functions. Here's a sample approach with the explanation.

with source_table as (select 
'Code identifying test 1
Acceptable values:
00=No code
01=Additional Code'  val
from dual)
select substr(val, instr(val, chr(10), 1, 2)+1 ,2) code
,substr(val, instr(val, '=', instr(val,chr(10), 1, 2), 1)+1 ,  (  instr(val,chr(10), 1, 3) - instr(val,'=', 1, 1)  ) ) description
from source_table;

Explanation

Step 1: Creating a table with data: Instead of actually creating a temporary table and adding the test data in, I'm spoofing the creation of a table using the WITH clause. In this case, I created a table with name source_table having one column val. It has one row of data which is the value in VAR_DESC column from row 1 of your source_table. You would not need to do this bit as you already have a table with data in your database.

with source_table as (select 
'Code identifying test 1
Acceptable values:
00=No code
01=Additional Code'  val
from dual)

Now for the actual string extraction. INSTR locates the position of a sub-string within a string. SUBSTR extracts a substring from within a string. So what I've done is, I've tried to identify a pattern in your data. The first data point to be extracted comes after the second newline (chr(10)) character. Once I had this position, I exracted a substring of length 2 from this point. The next sub-string to be extracted begins after the first = character and has a length which has been computed as the difference of the position of the 4th newline character from the first = character.

Similarly if you have multiple rows of strings that need to be extracted within the same row of source data, you need to apply similar logic.

An easier way to do it would be to leverage Oracle's PL/SQL capabilities and create a function (which internally breaks the string apart in steps) and then invoke the function in the SQL. The code can be made much more readable. You would use the same string functions within PL/SQL. But you can do it steps making the code easier to follow for someone else who might need to manage your code later on.

Improve this answer

1 Comment

Is there a way to extract all the codes available in the string separated by "=" and new line in 1 SQL query?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.