Need help for extracting substring from string till last space before certain length

Question

I`m trying to substring up to specific length. For example string length is 100. I need to cut the string at 31 characters but not to cut whole words.

PARTNER ESSENTIAL 24 MONTHS INITIAL FOR NETBACKUP SELF SERVICE XPLAT

If I simply split aft 31 characters the outcome is that the first part is PARTNER ESSENTIAL 24 MONTHS INI and the second part is TIAL FOR NETBACKUP SELF SERV, with the word INITIAL split. I need to end on ... 24 MONTHS and put the whole of the word INITIAL into the second part.

So I need to separate the string up to the last space to make this clear and understandable. One more thing this need to be executed in one single query.

What do you mean by first and second part - two columns in the same row of the result set? Or split to two separate rows? And how long can the second part be, up to 69 characters (as total is 100) or does that need to be split further too? — Alex Poole, Mar 26 '19 at 09:42

Peter Lang · Accepted Answer · 2019-03-26T15:39:56.570

2

You can use REGEXP_REPLACE. Take as many characters as possible (up to 31) followed by a space or the end of the string.

SELECT REGEXP_REPLACE(
  'PARTNER ESSENTIAL 24 MONTHS INITIAL FOR NETBACKUP SELF SERVICE XPLAT',
  '(.{1,31})( |$)(.*)',
  '\1' )
FROM dual;

PARTNER ESSENTIAL 24 MONTHS

Use '\3'instead to get the rest:

INITIAL FOR NETBACKUP SELF SERVICE XPLAT

edited Mar 26 '19 at 15:39

answered Mar 26 '19 at 09:39

Peter Lang

54,264
27
148
161

.. and for the OP's benefit, if the end of the pattern is in parentheses too i.e. `(.{1,31})( |$).(.*)` then the second half of the value can be retrieved with `\3`. – Alex Poole Mar 26 '19 at 09:51
Yes, this works! Thanks and now how to get second part into another column on same row. – SakBG Mar 26 '19 at 10:06
1

As Alex Poole already stated, you can do another REGEXP_REPLACE, put parenthesis around `.*` and use `\3` instead of `\1`. – Peter Lang Mar 26 '19 at 10:10
I have try with this `REGEXP_REPLACE(MyString, '(.{1,32})( |$).*', '\3' )` and like return I have no value at all. Am I make it wrong. Thanks again. – SakBG Mar 26 '19 at 15:29
1

I edited my answer to add the paranthesis that you missed. – Peter Lang Mar 26 '19 at 15:40

score 1 · Answer 2 · answered Mar 26 '19 at 09:46

You could use instr and substr to identify the relevant space position, with a case expression so you only try to split longer values:

-- CTE for sample data
with your_table (str) as (
  select 'PARTNER ESSENTIAL 24 MONTHS INITIAL FOR NETBACKUP SELF SERVICE XPLAT' from dual
  union all
  select 'PARTNER ESSENTIAL 24 MONTHS INI' from dual
  union all
  select 'PARTNER ESSENTIAL 24 MONTHS' from dual
  union all
  select 'PARTNER ESSENTIAL 24' from dual
)
-- actual query
select str,
  length(str) as str_len,
  case when length(str) <= 31 then str
       else substr(str, 1, instr(str, ' ', 31 - length(str), 1))
  end as part_1,
  case when length(str) <= 31 then null
       else substr(str, instr(str, ' ', 31 - length(str), 1) + 1)
  end as part_2
from your_table;

STR                                                                     STR_LEN PART_1                          PART_2                                                               
-------------------------------------------------------------------- ---------- ------------------------------- ---------------------------------------------------------------------
PARTNER ESSENTIAL 24 MONTHS INITIAL FOR NETBACKUP SELF SERVICE XPLAT         68 PARTNER ESSENTIAL 24 MONTHS     INITIAL FOR NETBACKUP SELF SERVICE XPLAT                             
PARTNER ESSENTIAL 24 MONTHS INI                                              31 PARTNER ESSENTIAL 24 MONTHS INI                                                                      
PARTNER ESSENTIAL 24 MONTHS                                                  27 PARTNER ESSENTIAL 24 MONTHS                                                                          
PARTNER ESSENTIAL 24                                                         20 PARTNER ESSENTIAL 24

score 1 · Answer 3 · answered Mar 26 '19 at 09:48

not as elegant as the regexp_replace variant already posted, but another way to do it, using substr and instr, in a step by step approach. I tried to make the code comment itself, let me know if there are any questions. HTH KR Peter

with 
 your_input as 
    (select 'PARTNER ESSENTIAL 24 MONTHS INITIAL FOR NETBACKUP SELF SERVICE XPLAT' string_ from dual)
,first_31_chars as
    (select substr(your_input.string_,1,31) substring_ from your_input)
,last_space_in_first_31_chars as
    (select instr(first_31_chars.substring_,' ',-1,1) position_ from first_31_chars)
,first_part as 
    (select trim(substr(string_,1,position_)) fp_substring_ from your_input,last_space_in_first_31_chars)
,second_part as 
    (select trim(substr(string_,position_)) sp_substring_ from your_input,last_space_in_first_31_chars)
select fp_substring_,sp_substring_ from first_part,second_part;

Need help for extracting substring from string till last space before certain length

3 Answers3