CODEWITHSUNDEEP
sqloracle-databasesubstring
SakBG
SakBG

Reputation: 47

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

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.

Upvotes: 2

Views: 316

Answers (3)

Peter Lang
Peter Lang

Reputation: 55524

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

Upvotes: 2

Peter
Peter

Reputation: 1196

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;

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191265

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

Upvotes: 1

Related Questions