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?
 
slightwv (䄆 Netminder) Commented:
Something like this:
select regexp_substr(fld1,'[^,]+') from table1
0
 
awking00Commented:
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
 
Alexander Eßer [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
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.

All Courses

From novice to tech pro — start learning today.