Limit a string to a specific substring

Posted on 2014-08-28
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 73

    Assisted Solution

    select rtrim(regexp_substr(pl,'^([^,]+,?){1,3}'),',') from yourtable
    LVL 8

    Assisted Solution

    select SUBSTR(str, 1,INSTR(str,',', 1, 3) -1) from table;
    LVL 34

    Assisted Solution

    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

    LVL 8

    Accepted Solution

    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

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

    Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    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…

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now