CODEWITHSUNDEEP
sqloracle10g
Casey
Casey

Reputation: 223

oracle substr function with delimiter scope

I am trying to find the nearest delimiter to a substr with a limit on the number of characters.

Eg: select 'oneone,twotwo, threethree, four,five,six,seven,eight,nine,ten,eleven,twelve' from dual;

Now, I want to cap the substr to pull only the first 30 characters from this string while also ensuring the substr end with the nearest "," if it cannot find the end point in the 30 characters.

select substr('oneone,twotwo, threethree, four,five,six,seven,eight,nine,ten,eleven,twelve',1,30) from dual ;-- First 30 characters.

The first 30 characters produces: oneone,twotwo, threethree, fou But, I want the expected result to find the nearest delimiter "," before the incomplete entry of "fou" and get the result.

Expected Result:oneone,twotwo, threethree -- since "fou" is incomplete and thus should be excluded

Upvotes: 0

Views: 5669

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

There's a good chance there is a simpler solution but

SQL> ed
Wrote file afiedt.buf

  1  with x as (
  2    select 'oneone,twotwo, threethree, four,five,six,seven,eight,nine,ten,eleven,twelve' str
  3      from dual
  4  )
  5  select substr( x.str,
  6                 1,
  7                 instr( substr( x.str, 1, 30 ),
  8                        ',',
  9                        -1 ) -1)
 10*   from x
SQL> /

SUBSTR(X.STR,1,INSTR(SUBS
-------------------------
oneone,twotwo, threethree

will work and, when you break it down, relatively easy to follow.

Working from the inside out,

substr( x.str, 1, 30 )

takes the first 30 characters of the string

instr( substr( x.str, 1, 30 ),
       ',',
       -1 )

gives you the position of the last comma in that 30 character string. So then

substr( x.str,
        1,
        instr( substr( x.str, 1, 30 ),
               ',',
               -1 ) -1)

takes the string from position 1 to the position just before the last comma in the 30 character string.

Upvotes: 1

Related Questions