Limit a string to a specific substring

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 ;
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

select rtrim(regexp_substr(pl,'^([^,]+,?){1,3}'),',') from yourtable
select SUBSTR(str, 1,INSTR(str,',', 1, 3) -1) from table;
johnsoneSenior Oracle DBACommented:
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

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;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DovbermanAuthor Commented:
All solutions are excellent.  This will increase my knowledge of PL/SQL.

Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.