Substr string function

Question

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

Sayan Malakshinov · Accepted Answer · 2020-09-18 10:23:30Z

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

Blu3H4x0r · Accepted Answer · 2020-09-18 10:06:01Z

0

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

    SUBSTR('string',1,100)

answered Sep 18, 2020 at 10:06

Blu3H4x0r

394 bronze badges

Comments

GMB · Accepted Answer · 2020-09-18 10:17:16Z

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.

edited Sep 18, 2020 at 10:17

answered Sep 18, 2020 at 10:07

GMB

224k25 gold badges103 silver badges151 bronze badges

5 Comments

monika Over a year ago

using above regexp output will be (monika awasthi awasthi awasthi awasthi) which exceeds 35 characters .

monika Over a year ago

I want max 35 characters in string & in case of word spliting it will print the string till last space from 35th character

Sayan Malakshinov Over a year ago

regular expression should be '^.{1,35}\s'

GMB Over a year ago

@SayanMalakshinov: quite, yes. We just need to manage the possibility that the string has less than 35 characters, so ` '^.{1,35}(\s|$)'`.

Sayan Malakshinov Over a year ago

@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

mkRabbani · Accepted Answer · 2020-09-18 10:24:16Z

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

answered Sep 18, 2020 at 10:24

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

Collectives™ on Stack Overflow

Substr string function

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related