Learn how to a build a cloud-first strategyRegister Now


Limit a string to a specific substring

Posted on 2014-08-28
Medium Priority
Last Modified: 2014-08-28
I have a function that iterates through rows of a cursor.

Each row of the cursor is a comma delimited set of string valaues.

"Name1, Name2, Name3, Name4"
"Name1, Name2"
"Name1, Name2, Name3"

I need to iterate through the rows as follows:

Most of this in in pseudocode.

intCommaCount := 0;
FOR cur_rec in CUR_CURSOR
        select cur_rec.PL into strLetterNameGroup from dual;
           -- count the number of commas as intCommaCount
           if intCommaCount >=3  --"Name1, Name2, Name3, Name4" ;
                      --strLetterNamesGroup needs to be "Name1, Name2, Name3 ;
                     -- extract the substring only to the character to the left of the 3rd comma
           end if
      end loop;

  return strLetterNameGroup ;
Question by:Dovberman
LVL 74

Assisted Solution

sdstuber earned 400 total points
ID: 40290146
select rtrim(regexp_substr(pl,'^([^,]+,?){1,3}'),',') from yourtable

Assisted Solution

vinurajr earned 1000 total points
ID: 40290156
select SUBSTR(str, 1,INSTR(str,',', 1, 3) -1) from table;
LVL 35

Assisted Solution

johnsone earned 600 total points
ID: 40290178
The SUBSTR/INSTR solution will not work if there are 3 or less elements in the list.  If you want that solution to work with lists of 3 or less, then you need to account for the INSTR returning 0 in that case.  Something like this:

SELECT Substr(col1, 1, CASE 
                         WHEN Instr(col1, ',', 1, 3) > 0 THEN 
                         Instr(col1, ',', 1, 3) - 1 
                         ELSE Length(col1) 
FROM   mytab; 

Open in new window


Accepted Solution

vinurajr earned 1000 total points
ID: 40290191
I just re-write this way:

SELECT CASE WHEN Instr(col1, ',', 1, 3) > 0 THEN
                              Substr(col1, 1, Instr(col1, ',', 1, 3) - 1)
                         ELSE col1
FROM   mytab;

Author Closing Comment

ID: 40290672
All solutions are excellent.  This will increase my knowledge of PL/SQL.

Thank you.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

810 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