Oracle query to get first part of string

I need oracle query for following
   
  The Fld1 in Table1  has values like  
     StringNoComma
      ThisWith,Comma

   I need to get the full string if there is no comma. Only first part if there is a comma

  I need to get the result
     StringNoComma
      ThisWith
Sam OZAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

slightwv (䄆 Netminder) Commented:
Something like this:
select regexp_substr(fld1,'[^,]+') from table1
0

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
awking00Information Technology SpecialistCommented:
While I like the regexp method, I thought this was kind of cute - :-)
select substr(fld1,0,decode(instr(fld1,','),0,length(fld1),instr(fld1,',') - 1))
from table1;
0
Mark GeerlingsDatabase AdministratorCommented:
Either of those suggestions should work.  I’m not an expert with regular expressions like the one suggested by slightwv, but when they can be used, they tend to be faster than what awking00 suggested.  I find awking00’s suggestion easier to understand, but if you need to use this multiple times, or for lots of records, use the one from slightwv, assuming that produces the correct results.
0
Alex [***Alex140181***]Software DeveloperCommented:
@Mark: Just for clarity:
RegEx functions in Oracle are NOT faster than the built-ins, e.g.
https://stackoverflow.com/questions/41156391/performance-and-readability-of-regexp-substr-vs-instr-and-substr
0
slightwv (䄆 Netminder) Commented:
I dislike blanket generalizations.

Regular expressions can be much slower if you do complex matching.

If you need to be concerned with every second of processing then I suggest you set up your own tests.  If you don't, I find the regex much easier to understand.  That is why I didn't post any alternative.  Follow KISS whenever possible.

When it comes to decode vs regular expressions there is a huge difference that can easily decide:  DECODE has to be used in a select.  REGEXP_SUGSTR doesn't.

I give you this test to review.
For 10 million executions the difference between regex and decode is a fraction of a second.  BUT, that is switching context out of PL/SQL to do the SELECT.  Compare that wit direct assignment and REGEX is much faster since it doesn't have to leave PL/SQL.  The decode fails with the same syntax.

SQL> set timing on
SQL> declare
  2  junk varchar2(10);
  3  begin
  4  for i in 1..1000000 loop
  5  select substr('Hello',0,decode(instr('Hello',','),0,length('Hello'),instr('Hello',',') - 1)) into junk from dual;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.35
SQL>
SQL> declare
  2  junk varchar2(10);
  3  begin
  4  for i in 1..1000000 loop
  5  select regexp_substr('Hello','[^,]+') into junk from dual;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.69
SQL>
SQL> declare
  2  junk varchar2(10);
  3  begin
  4  for i in 1..1000000 loop
  5  junk := regexp_substr('Hello','[^,]+');
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> declare
  2  junk varchar2(10);
  3  begin
  4  for i in 1..1000000 loop
  5  junk := substr('Hello',0,decode(instr('Hello',','),0,length('Hello'),instr('Hello',',') - 1));
  6  end loop;
  7  end;
  8  /
junk := substr('Hello',0,decode(instr('Hello',','),0,length('Hello'),instr('Hello',',') - 1));
                         *
ERROR at line 5:
ORA-06550: line 5, column 26:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL
statement only
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored


Elapsed: 00:00:00.03
SQL>

Open in new window

0
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.