CODEWITHSUNDEEP
sqloracle-database
user1501309
user1501309

Reputation: 33

splitting strings in oracle sql based on length

I want to split my strings in Oracle based on length with space as a delimiter.

For example,

MY_STRING="welcome to programming world"

My output should be 

STRING1="welcome to "
STRING2="programming "

The strings should be a maximum of 13 characters in length. The words after position 26 can be ignored.

Upvotes: 3

Views: 4726

Answers (1)

Ben
Ben

Reputation: 52863

You don't mention what version of Oracle you're using. If you're using 10g or above you can use regular expressions to get what you need:

with spaces as (
 select regexp_instr('welcome to programming world' || ' '
                    , '[[:space:]]', 1, level) as s
   from dual
connect by level <= regexp_count('welcome to programming world' || ' '
                                , '[[:space:]]')
        )
, actual as (
 select max(case when s <= 13 then s else 0 end) as a
      , max(case when s <= 26 then s else 0 end) as b
   from spaces
        )
select substr('welcome to programming world',1,a)
     , substr('welcome to programming world',a, b - a)
  from actual

This finds the positional index of all the spaces, then finds the one that's nearest but less than 14. Lastly uses a simple substr to split your string. The strings will have a trailing space so you might want to trim this.

You have to concatenate your string with a space to ensure that there is a trailing space so the last word doesn't get removed if your string is shorter than 26 characters.

Assuming you're using an earlier version you could hack something together with instr and length but it won't be very pretty at all.

Upvotes: 1

Related Questions