How to remove word in the beginning of the string and all after the certain string?-CodePudding

I am trying to remove words from the string, for instance:

select 'Lorem ipsum dolor sit amet consectetur adipisicing elit.'
...

I want to get the ipsum dolor sit - to remove the first word, and all after the "sit"

Thanks in advance!

CodePudding user response：

Use backreferences for this. First split your string up in 3 groups: (1) what you don't want before your string (2) the actual string (3) whatever comes after your string.

(1) zero or more characters starting at the beginning for the replace_string: (^.*)
(2) actual string (ipsum dolor sit)
(3) zero or more characters starting after replace_string to the end of the replace_string: (.*$)
(4) replace only with actual string \2

with test_data (c) as (

select 'Lorem ipsum dolor sit amet consectetur adipisicing elit.' from dual UNION ALL
select 'ipsum dolor sit amet consectetur adipisicing elit.' from dual UNION ALL
select 'Lorem ipsum dolor sit' from dual

)
select REGEXP_REPLACE(c,'(^.*)(ipsum dolor sit)(.*$)','\2') from test_data;

ipsum dolor sit
ipsum dolor sit
ipsum dolor sit

CodePudding user response：

You can use simple string functions (which are much faster that regular expressions):

SELECT CASE 
       WHEN sit > 0
       THEN SUBSTR(
              value,
              first_space   1,
              sit   3 - first_space
            )
       END AS substring
FROM   (
  SELECT value,
         INSTR(value, ' ') AS first_space,
         CASE
         WHEN INSTR(value, ' sit ') > 0
         THEN INSTR(value, ' sit ')
         WHEN value LIKE '% sit'
         THEN LENGTH(value) - 3
         ELSE 0
         END AS sit
  FROM   table_name
);

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'Lorem ipsum dolor sit amet, consectetur adipiscing elit' FROM DUAL UNION ALL
SELECT 'ipsum dolor sit amet, consectetur adipiscing elit' FROM DUAL UNION ALL
SELECT 'Lorem ipsum dolor sit' FROM DUAL UNION ALL
SELECT 'dolor sit' FROM DUAL UNION ALL
SELECT 'sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.' FROM DUAL UNION ALL
SELECT 'First situation sitar sit site' FROM DUAL;

Outputs:

SUBSTRING

ipsum dolor sit

dolor sit

ipsum dolor sit

sit

null

situation sitar sit

SUBSTRING
ipsum dolor sit
dolor sit
ipsum dolor sit
sit
null
situation sitar sit

If you did want to use (slow) regular expressions then:

SELECT REGEXP_SUBSTR(value, '\s((.*?\s)?sit)(\s|$)', 1, 1, NULL, 1) AS substr
FROM   table_name

Which outputs the same.

db<>fiddle here