0

I want to get the string of 35 characters from starting but every word should not split.e.g.

select SUBSTR('monika awasthi awasthi awasthi awasthi',1,35) from dual

output is :monika awasthi awasthi awasthi awas

but if my string end is in the middle of the word then output should be print till last space:e.g

Desired output:

monika awasthi awasthi awasthi
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
monika
  • 67
  • 7

4 Answers4

1

You just need substr + instr with negative 3rd parameter: from the doc by this link:

If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.

So it would be simple

substr(str, 1, instr(str,' ', (35 - length(str))))

Another approach is to use regexp_substr: regexp_substr('monika awasthi awasthi awasthi awasthi', '^.{1,35}\s')

Full example:

select
    str
   ,substr(str, 1, instr(str,' ', (35 - length(str)))) substring
   ,regexp_substr('monika awasthi awasthi awasthi awasthi', '^.{1,35}\s') substring2
from (select 'monika awasthi awasthi awasthi awasthi' str from dual);

Results:

STR                                    SUBSTRING                           SUBSTRING2
-------------------------------------- ----------------------------------- -------------------------------
monika awasthi awasthi awasthi awasthi monika awasthi awasthi awasthi      monika awasthi awasthi awasthi
Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27
0

Just expand the substr from 35 to a larger length. For instance:

    SUBSTR('string',1,100)
Blu3H4x0r
  • 39
  • 4
0

This might be simpler expressed with a regex:

regexp_substr('monika awasthi awasthi awasthi awasthi', '^.{1,35}(\s|$)')

^.{1,35}(\s|$) means: maximum 35 characters from the start of the string, ending with a space or the end of string.

You can rtrim() the result is you want to get rid of the potentially trailing space.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • using above regexp output will be (monika awasthi awasthi awasthi awasthi) which exceeds 35 characters . – monika Sep 18 '20 at 10:11
  • I want max 35 characters in string & in case of word spliting it will print the string till last space from 35th character – monika Sep 18 '20 at 10:13
  • regular expression should be `'^.{1,35}\s'` – Sayan Malakshinov Sep 18 '20 at 10:16
  • @SayanMalakshinov: quite, yes. We just need to manage the possibility that the string has less than 35 characters, so ` '^.{1,35}(\s|$)'`. – GMB Sep 18 '20 at 10:18
  • @GMB I'd avoid using regex functions in such cases using `case when length(str)<=35 then str else regexp_substr...`. Regex functions are too slow – Sayan Malakshinov Sep 18 '20 at 10:21
0

Try this below option-

DEMO HERE

select 
case 
    when INSTR(str,' ',35) = 0 then str
    else SUBSTR(str, 1, INSTR(str,' ',35))
end
from cte
mkRabbani
  • 16,295
  • 2
  • 15
  • 24